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 ...
Need Help
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....

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

        The issue is when i relate my date table with my actual table than it does not let me filter through date heirarchy.