Date measure 2020 data in 2021

[Nick2358]

Hello everyone,

I have a report that updates data monthly thanks to the following measure:

2020_2_Months_Ago = var current_month = MONTH(TODAY()) return CALCULATE(SUM('Conso Bdgt 2020'[SUM]), FILTER('Dates', MONTH('Dates'[Date]) = current_month -2))

The numbers are 2 months late so as of right now (January 2021) I need to see data from November 2020.

It was wroking fine but since we are now in 2021 it only shows blanks (since it refers to "current_month -2" I thought it would work).

Does anyone have an idea how to solve this?

Thanks,

Nick,

[ToddChitt · Accepted solution]

@Nick2358 

I usually include a Date dimension with several attributes. One nice attribute is [Months Back] and is defined as the number of months between the date in question and today. So for any date in November 2020, that value would be 2. 

Try DATEDIFF in DAX. DATEDIFF function (DAX) - DAX | Microsoft Docs

I have done this either in SQL with DATEADD and GETDATE() functions, or Power Query, or even in DAX. I will leave it to you to choose what works best. But since it is always in reference to today, the numbers are dynamic. 

You can then write the Measure as:

Two Months Ago = CALCULATE ( Sum ( [column] ), Dates[Months Back] = 2 )

[amitchandak · Accepted solution]

@Nick2358 , Chnage it like

2020_2_Months_Ago =
var current_month = eomonth(TODAY(),-2)
return CALCULATE(SUM('Conso Bdgt 2020'[SUM]), FILTER('Dates', eomonth('Dates'[Date],0) = current_month))

But my advice should be use datesmtd or previousmonth

previous to previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

also refer my video Why Time intellignce fails- https://www.youtube.com/watch?v=OBf0rjpp5Hw

[Icey · Accepted solution]

Hi @Nick2358 ,

Try to create a measure like so:

2_Months_Ago =
CALCULATE (
    SUM ( 'Conso Bdgt 2020'[SUM] ),
    DATESINPERIOD ( 'Dates'[Date], MAX ( 'Date'[Date] ), - 2, MONTH )
)

Best regards

Icey

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

[Nick2358]

@Icey @amitchandak @ToddChitt 

Sorry for the delay I had to apply it to my whole dashboard and make sure it works.

I tried all of them and they all worked so I accepted all of your solutions.

Thanks for your help,

Nick,

[amitchandak · Accepted solution]

@Nick2358 , Chnage it like

2020_2_Months_Ago =
var current_month = eomonth(TODAY(),-2)
return CALCULATE(SUM('Conso Bdgt 2020'[SUM]), FILTER('Dates', eomonth('Dates'[Date],0) = current_month))

But my advice should be use datesmtd or previousmonth

previous to previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

also refer my video Why Time intellignce fails- https://www.youtube.com/watch?v=OBf0rjpp5Hw

[ToddChitt · Accepted solution]

@Nick2358 

I usually include a Date dimension with several attributes. One nice attribute is [Months Back] and is defined as the number of months between the date in question and today. So for any date in November 2020, that value would be 2. 

Try DATEDIFF in DAX. DATEDIFF function (DAX) - DAX | Microsoft Docs

I have done this either in SQL with DATEADD and GETDATE() functions, or Power Query, or even in DAX. I will leave it to you to choose what works best. But since it is always in reference to today, the numbers are dynamic. 

You can then write the Measure as:

Two Months Ago = CALCULATE ( Sum ( [column] ), Dates[Months Back] = 2 )