DAX Previous Month Function Not working

[Sandeep_PBI]

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?

[CheenuSing]

@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]

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]

@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]

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.

[CheenuSing]

@Sandeep_PBI

Can you share the data model,sample data and expected output to probe further.

Cheers

CheenuSing