Use DAX to calculate sales for last full month in period

0
votes

My filter context contains all dates in a financial year (eg. 1-Jul-2017 - 30-Jun-2018). I would like to calculate sales for the most recent full month in the selected financial year.

Eg. For the current financial year 1-Jul-2017 - 30-Jun-2018, I would like to calculate total sales for August, since it is the most recent full month in the period as of today (15-Sep-2016). I have tried the following:

Prem $ Last Closed Month = CALCULATE(SUMX(SalesFlat, [gross_amt_plus_lhc_annual]), DIM_DATE[MONTH_END_DATE] < NOW()) - CALCULATE(SUMX(SalesFlat, [gross_amt_plus_lhc_annual]), DATEADD(DIM_DATE[MONTH_END_DATE], 1, MONTH) < NOW())

But am getting the error "A function 'DATEADD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Any ideas?

Thanks.

2
powerbidax
Do you have a reproducible example with some sample data? – user5226582

2 Answers

0
votes

Try using PREVIOUSMONTH(<dates>) function

Prem $ Last Closed Month = SUMX(PREVIOUSMONTH(<dates>),calculate(sum(TotalSales))

Replace TotalSales with your measure for sales.

0
votes

Maybe this will help. If I start with this table, named "SalesFlat"...

enter image description here

...and I add a column using this code...

MostRecentFullMonth = SUMX(FILTER(SalesFlat,EOMONTH(SalesFlat[MONTH_END_DATE],0)=EOMONTH(NOW(),-1)),SalesFlat[gross_amt_plus_lhc_annual])

...I get this result:

enter image description here

Since today is 16 September 2017, the most recent full month was August 2017. The total gross_amt_plus_lhc_annual for August 2017 was 43 + 66 + 98 + 58 + 9 = 274.

You could also use this to set up a measure, instead of a column, and you can use it in your PowerBI dashboard like with this card:

enter image description here