Forum Discussion
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 from each dimesion table.
It works fine until i add my measure below. Is there a way to optimize this measure? It seem so simple that I shouldn't have this issue but I know DAX is complex and am hoping there is an obvious solution I am overlooking.
Revenue 12MM =
VAR revenue12mm = CALCULATE([SUM( Invoice[Revenue] ),
REMOVEFILTERS(Dates[Date]),
DATESINPERIOD(Dates[Date], LASTDATE(Invoice[ReportingDate]), -12, MONTH)
)
RETURN
IF( revenue12mm = 0, BLANK(), revenue12mm)
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.
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.
5 Replies
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.
Hi nbs33 ,
Has your problem been solved?If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
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.
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.
