Help with PREVIOUSMONTH

Time intelligence functions require all the dates without any gap so I don't think there is any option here for you other than create a date table with date granularity.

andybamber - You cannot get the PREVIOUSMONTH function to work in that case. The DAX time "intelligence" functions are little black boxes of functionality that only work when certain, specific criteria are followed. And part of that certain, specific criteria is almost always a date table or at the very least, dates.

 

This may help: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-NEXTDAY-Lone-Biker-of-the-Apocalypse/m-p/1245497#M574

 

These may also help:

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Hey andybamber ,

 

there is a quite short answer to your question: You can't.

 

PREVIOUSMONTH as most of the other time intelligence functions requires a dedicated date table without gaps.

 

You might consider using the following approach:

• Get the current date var _currentDate = MAX('periodtable'[date])
• Get the endofmonth of the previous month _eomPrevMonth = EOMONTH(_currentDate , -1)
• Now get the start of the previous month var _startOfPrevMonth = DATE(YEAR(_eomPrevMonth) , MONTH(_eomPrevMonth) , 1)

You can use the variable to filter your period table like so by creating a measure like so:

measure = 
var _currentdate = ...
var _eomPrevMonth = ...
var _startOfPrevMonth = ...
return
CALCULATE(
<your numeric expression>
, 'periodtable'[date] = _startOfPrevMonth
)

Hopefully, this provides an idea on how to tackle your challenge.

 

Regards,

Tom

 

andybamber , for any time intelligence to work correctly you need to have a date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
previous month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

 

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.