cancel
Showing results for
Did you mean:  Helper III

## Date measure 2020 data in 2021

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,
3 ACCEPTED SOLUTIONS  Post Prodigy

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 )  Super User

@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

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Community Support

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.

4 REPLIES 4  Community Support

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.  Helper III

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.

Nick,  Super User

@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

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Post Prodigy

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 )   