cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nick2358
Helper III
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
ToddChitt
Post Prodigy
Post Prodigy

@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 )

View solution in original post

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

Icey
Community Support
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.

View solution in original post

4 REPLIES 4
Icey
Community Support
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.

View solution in original post

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

ToddChitt
Post Prodigy
Post Prodigy

@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 )

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.