The total in a measure is different than expected

DAX DAX Calculations
1

Hello
My dax measure is like this:
Sales LY = IF(ISBLANK([Net Sales]), BLANK(),
CALCULATE([Net Sales], DATEADD(‘Tab Dates’[Date], -1, YEAR)))

which means the total that i’m seing in the visual is giving me the total of all year instead of just the months that i’m seing…
The column Sales LY has a wrong total
Capture

Can it b fixed?

2

Hello @pedroccamara,

Thank You for posting your query onto the Forum.

You can try the formula given below -

Sales LY = 
SUMX(
   SUMMARIZE( Dates , Dates[Year & Month] , "Last Year Sales" , [Sales LY] ) , 
"Last Year Sales" )

I’m also providing a link below where one of our expert @Greg had created a post which addresses specifically this issue.

Hoping this helps you. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

1 Like
3

Hello @Harsh
Thanks for your answer.
“your” formula doesn’t work. It says “can’t display the visual”

Sales LY Test1 = SUMX(
SUMMARIZE( ‘Tab Dates’, ‘Tab Dates’[Year & Month], “Last Year Sales” , [Sales LY] ), “Last Year Sales” )

Then i went to Greg’s link and also didn’t work because it showed me some values from 2019 and others from 2020

Sales LY Test2 = SUMX(
ADDCOLUMNS(
VALUES( ‘Tab Dates’[Year & Month]),
“Sales”, [Net Sales],
“Sales LY”, [Sales LY] ),
MIN( [Net Sales], [Sales LY] ))
Capture

4

Hello @pedroccamara,

You can try to replace it with ‘Tab Dates’[Dates].

If this doesn’t work out then could you please upload your file?

And the second formula that you’ve written is used to find the minimum sales for the last year or for the current year.

Thanks & Warm Regards,
Harsh

5

What is the total of 2019?

6

622.574€

7

But that is the total for 2020 as per your visual.

8

Does this help?

this is from the public Youtube from enterprise DNA

2 Likes
9

sorry… 620.141€

10

Can you please provide the PBI file.

11

Excellent tip @Despo. Yes the video helped.
My measure now:

Sales LY3 = SUMX(
SUMMARIZE( ‘Tab Dates’, ‘Tab Dates’[Year & Month],
“Sales LY”, [Sales LY]),
[Sales LY] )

Thank you so much

2 Likes