Forum Discussion

afaque03's avatar
afaque03
Icon for Helper I rankHelper I
8 years ago

To calculate previous month and current month average value.

Hi,

I have a Total field and an order date field I want to calculate the current month and previouds month average value using these fields.

 

I have made a DAX function -:

 

Average Last Month = CALCULATE(SUM('Purchase Data'[Total]),PARALLELPERIOD('Purchase Data'[Order Date],-1,MONTH))

 

But this DAX does not give me the average for last month and give me an error while rendering in report.

 

Please help me with the above query.

Need Help

4 Replies

  • afzalphatan's avatar
    afzalphatan
    Icon for Resolver I rankResolver I
    8 years ago

    You should have seperate dimension date table staringting from Jan 1st to 31st december (one to many relation with fact table) in order to use DAX date functions without errors.

     

    Try using DATEADD() function... This is best one to alter date table. 

     

    Hope this helps... Else post sample data and expected results pic so that we help further

     

    Regards

    Afzal kha

    • afaque03's avatar
      afaque03
      Icon for Helper I rankHelper I
      8 years ago

      I tried this function

       

      Average Last Month = CALCULATE(SUM('Purchase Data'[Total]),DATEADD('Purchase Data'[Order Date],-1,MONTH))

       

      This didnt worked for me

      • afzalphatan's avatar
        afzalphatan
        Icon for Resolver I rankResolver I
        8 years ago

        You need to have seperate Date table from Jan 1st to Dec 31st and then.... connect it with ur fact table ... later ur can use DATEADD()

        for correct result....