Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
donesb87
Regular Visitor

Use a measure to filter dates to end of previous month

Hi

 

Firstly, I've only just starting using Power BI so am by no means an expert.

 

I'm working with an existing document that contains a particular measure:

 

Cumulative forecasts in future = CALCULATE(SUM('Forecast Report Oct 2016'[Forecast amount]),FILTER(ALL('Calendar'[Date]),AND('Calendar'[Date]>TODAY(),'Calendar'[Date]<=MAX('Calendar'[Date]))))

 

When i refresh this data now (in October) the forecasts for October do not appear, i'm assuming because of the >TODAY part of the measure.

 

Can anyone please provide a suggested update to this measure, perhaps changing the >TODAY to something about the end of the previous month so that the October forecasts are show?

 

Thank you!

1 REPLY 1
Greg_Deckler
Super User
Super User

How about something like:

 

Spoiler
Cumulative forecasts in future = CALCULATE(SUM('Forecast Report Oct 2016'[Forecast amount]),FILTER(ALL('Calendar'[Date]),AND('Calendar'[Date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),'Calendar'[Date]<=MAX('Calendar'[Date]))))

For today, that should return 10/1/2018 in the filter

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.