Forum Discussion

nbs33's avatar
nbs33
Helper II
3 years ago
Solved

Optimizing a simple moving average measure

Hi,   I have a Invoice Fact table with about 300,000 roles , 4 Dimension tables and a Date table with one to many relationships. I am trying to create a simple table with Invoice # and a column fro...
Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  nbs33 ,

     

    According to your description, you want to calculate the previous year's revenue , right?

     

    You can try to create a measure.

     

    Revenue 12MM =

VAR revenue12mm = CALCULATE(SUM(Invoice[Revenue]), PARALLELPERIOD(Dates[Date],-1,year))  

RETURN

IF( revenue12mm = 0, BLANK(), revenue12mm)

     

    For more information about the function you can refer to the following documents.

    PARALLELPERIOD function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • nbs33's avatar
    nbs33
    3 years ago

    After a lot of trial and error and looking at the query analizer I relized most of the time was spent joining the dimension tables to the fact table. 

     

    To overcome this I colapsed the necessary columns of my demnsion table into may fact table and this greatly improved performance. 

nbs33's avatar
nbs33
Helper II
3 years ago

After a lot of trial and error and looking at the query analizer I relized most of the time was spent joining the dimension tables to the fact table. 

 

To overcome this I colapsed the necessary columns of my demnsion table into may fact table and this greatly improved performance.