Forum Discussion

Sandeep_PBI's avatar
Sandeep_PBI
Frequent Visitor
9 years ago

DAX Previous Month Function Not working

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 DA...
Need Help
Sandeep_PBI's avatar
Sandeep_PBI
Frequent Visitor
9 years ago

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's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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.