Optimizing a simple moving average measure

[nbs33]

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)

[v-tangjie-msft · Accepted solution]

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 · Accepted solution]

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. 

[v-tangjie-msft · Accepted solution]

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. 

[tamerj1]

Hi @nbs33 

try to replace LASTDATE with MAX

[v-tangjie-msft]

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. 

[nbs33]

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