Re: DAX Previous Month Function Not working

Sandeep_PBI · ‎09-22-2016

Hi,

I have a simple excel sheet with 2 columns(Month, Sales) with sales related data for 4 months.

And all i am trying to do is create a new colum which gives me last month's sales using a DAX function against each month.

I have tried measures with following calculations

Measure_TotalSales = SUM('Sheet1 (2)'[sales])

Measure_PrevMonthSales = CALCULATE([Measure_TotalSales],dateadd('Sheet1 (2)'[Month],-1,MONTH))

Measure_PrevMonnth_PREVIOUSMONTH = CALCULATE(SUM('Sheet1 (2)'[sales]),PREVIOUSMONTH('Sheet1 (2)'[Month]))

I have tried to create a calculated column with following calculations

Column_PrevMonthSales = CALCULATE([Measure_TotalSales],dateadd('Sheet1 (2)'[Month],-1,MONTH))

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

on top of that i have also tried

http://www.dutchdatadude.com/power-bi-pro-tip-making-date-time-calculations-work-time-intelligence/

and downloded files from Web and copied formulas as specified in the following link

http://powerbi.tips/2016/07/measures-month-to-month-percent-change/

followed step by step as specified the teh link above, but i still see blank rows for the prev month calc.

Could this be a bug or do i need to use a different version of PBI desktop?

Anonymous · ‎09-22-2016

@Sandeep_PBI

Firstly for time intelligence funtions to work properly, you must have a Date Field.

I notice in your

Measure_PrevMonnth_PREVIOUSMONTH = CALCULATE(SUM('Sheet1 (2)'[sales]),PREVIOUSMONTH('Sheet1 (2)'[Month]))

you are refering to Month and not on Date column.

Can you try amending your measures.

If it works please accept this as solution and also give KUDOS.

Cheers

CheenuSing

Sandeep_PBI · ‎09-23-2016

Hi,

Thanks for your response, but even though the name says month, it actually is a date fiel. i createde this sample data and made sure the format is date from the modelling options for the fiel.

All i was able to prodce was a grand total of previous month's sales. meaning, for the Previous month column i see blanks agaist each month (After grouping by month for the date field) but there is a grand total which shows up for that column and the number is correct , it is sum of all the months except the first one!

v-ljerr-msft · ‎09-26-2016

@Sandeep_PBI

Most of the time intelligence functions require a standard Date table to work correctly. In this scenario, you can use CALENDAR function to create the Date table, and use Month column to create a relationship between your fact table and this Date table. Then PREVIOUSMONTH function should work. The formula below is for your reference.

Measure_PrevMonthSales =
CALCULATE ( [Measure_TotalSales], PREVIOUSMONTH ( 'DateTable'[Date] ) )

Measure_PrevMonthSales =
CALCULATE ( [Measure_TotalSales], DATEADD ( 'DateTable'[Date], -1, MONTH ) )

Regards

Anonymous · ‎11-01-2017

Hi, if one is stuck using Excel 2010 (in which the CALENDAR function is not available), how can you get PREVIOUSMONTH to work? I also made my own date table, in which all the dates are of the date data type, but PREVIOUSMONTH doesn't seem to work.