topic Optimizing a simple moving average measure in DAX Commands and Tips

Optimizing a simple moving average measure

nbs33 — Thu, 06 Oct 2022 17:11:56 GMT

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)

Re: Optimizing a simple moving average measure

tamerj1 — Thu, 06 Oct 2022 17:33:27 GMT

Hi nbs33

try to replace LASTDATE with MAX

Re: Optimizing a simple moving average measure

nbs33 — Thu, 06 Oct 2022 18:28:39 GMT

Hi tamerj1 thanks for the help. It didn't work. Could it be realted to DATESINPERIOD?

Re: Optimizing a simple moving average measure

Anonymous — Fri, 07 Oct 2022 09:49:03 GMT

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.

Re: Optimizing a simple moving average measure

Anonymous — Fri, 21 Oct 2022 08:59:40 GMT

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.

Re: Optimizing a simple moving average measure

nbs33 — Thu, 03 Nov 2022 19:11:00 GMT

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.