cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User IV
Super User IV

Re: Use a measure to filter dates to end of previous month

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

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors