Use a measure to filter dates to end of previous month

[donesb87]

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!

[Greg_Deckler]

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