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

    • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Sandeep_PBI

     

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

     

    Cheers

    CheenuSing