DAX What's wrong with my measure to return the value of the previous month

[FredLEGUEN]

Hi Group,

I have created a dashboard to follow the Covid19 in France.

I never had issues with my measure but today, I have noticed a problem with the result of my measure to return the value of the previous month.

Here is my measure to return the number of deaths 1 month ago

DeathM-1 = CALCULATE([DCLast],DATEADD(TimeTable[Date],-1,MONTH))

But the result is impossible. You can't have more deaths 7 days ago (24156) compare to 1 month ago (25113)

But if I replace my original measure by this one, the result is better

DeathD-30 = CALCULATE([DCLast],DATEADD(TimeTable[Date],-30,DAY))

I never had problem with my original measure. 

Do you have an idea why suddenly, the result is wrong?

[v-rzhou-msft]

Hello @FredLEGUEN

In additon to HotChilli's response, I think DATEADD(TimeTable[Date],-1,MONTH) will return at the end of last month.

So 25113 is the sum of death from the start date to 2020/10/31.

I build a sample table for you to test.

The result of your measurement will return to 31, which is the sum from the start date to the end of October.

You can test my measurement:

DeathM-1 = 
CALCULATE([DCLast],FILTER(TimeTable,TimeTable[Date]<= EOMONTH(TODAY(),-2)+DAY(TODAY())))

The result will be back to 6 which is added from 2020/10/01 to 2020/11/06(Today) - 31(Oct has 31 Days).

You can download the pbix file from this link: DAX What's wrong with my measure to return the previous month's value

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[HotChilli]

Why do you say 'Of course not'?  You posted the issue yesterday(Nov 3rd).  It looks entirely plausible that 25113 is the value at the end of October.

What I'm saying is - the measure appears to have given you the value for the end of October when you wanted a value from around Oct 3rd.  Perhaps you could check the data by getting the value for the end of Oct with a different measure.  We can then investigate what's going wrong with the original measure. 

[FredLEGUEN]

Hi @HotChilli 

I have send you a private message

[HotChilli]

Is it possible that 25113 is the value of the measure DCLast at the end of October (the previous month)? 

[FredLEGUEN]

Hi @HotChilli 

Of course not, and this is why I have immediately noticed a problem

@amitchandak 

Thanks for your ideas of measures but even I follow your explanation, I still don't have the correct value.

First, I have suppected a problem with my time table but if it was the case, the measure with -30 days won't return the result. 

So here, I really don't understand what's wrong with the measure.

[amitchandak]

@FredLEGUEN , Try a measure like

DeathM-1 = CALCULATE([DCLast],datesmtd(DATEADD(TimeTable[Date],-1,MONTH)))

also prefer to use date table in all such case, refer examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.