Help with PREVIOUSMONTH

[andybamber]

Hello! I'm hoping someone could help me with a query regarding the PREVIOUSMONTH function.

I have a fact table containing a column of sales figures, the same table also contains a period key... it doesnt contain any actual dates. This table is linked to a period table using the period key. The period table contains a date column, but the dates are month beginning dates.. so 1/1/20, 1/2/20 etc. It does not contain day by day dates.

My question is how i can make the PREVIOUSMONTH function work correctly given the above situation, as a date table with just month beginning dates will not work with the PREVIOUSMONTH function...

Thanks in anticipation!

Andy

[amitchandak]

@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-Y...


Appreciate your Kudos.

[TomMartens]

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

[Greg_Deckler]

@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-Apoc...

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-TIT...

[AntrikshSharma]

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.