Forum Discussion
DAX Previous Month Function Not working
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
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!
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
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.
Can you share the data model,sample data and expected output to probe further.
Cheers
CheenuSing
