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 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?

 

 

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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'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