DAX Calculate

Resolved · High Priority · Version 365

Mon 20 Apr 2020, 19:37 Delegate Tsudoi said...

Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course

DAX Calculate

How can I get last year sales in the same row as this year sales in pivot table (=keeping external filter alive) using Calculate function?
If I use SAMEPERIDOLASTYEAR and have external filter 2020, the value shows in 2019 row, not in the same row as 2020. I want to show 2020 and 2019 sales values in the same row in the same pivot table.
There must be some row contact filtering that I can apply to Calculate function but I don’t know how. Please can you advise?

Thanks,

Wed 22 Apr 2020, 09:59 Trainer Jens said...

RE: DAX Calculate

Hi Tsudoi,

Thank you for the forum question.

If you filter your PivotTable to show 2020 and both have your sales measure (Total Sales:=sum(SalesTable[Sales amount])) and your Calculate (Sales Last Year:=Calculate([Total Sales],Sameperiodlastyear(DateTable[DateKey]))) as values in the PivotTable then the PivotTable should show 2020 sales in the Total Sales column and in the same row but in the "Sales Last Year" column the total sales for 2019.

As far as I remember you are working with one large table and a DateTable in your model.

When you are talking about external filter I assume that you mean a slicer or you have added "Year" to Filter in the PivotTable Fields pane.

Please let me know if I misunderstand your issue.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Sat 25 Apr 2020, 20:56 Delegate Tsudoi said...

RE: DAX Calculate

Many thanks Jens

Mon 27 Apr 2020, 12:05 Trainer Jens said...

RE: DAX Calculate

You are welcome Tsudoi

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 4 May 2020: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Formst Excel to display leading zeroes

Select the cells that you want to have displaying leading zeroes.

From the 'Format' menu select 'Cells' (Or Right-click on the selected range and and select 'Format Cells').

In the 'Number' tab click on 'Custom' in the 'Category' window.

In the 'Type:' box enter zeroes that correspond to the size of the required number (eg 5 zeroes).

Click OK.

When you enter numbers into these cells, leading zeroes will be displayed.

eg 123 = 00123

View all Excel hints and tips