topic Re: Slow performance of last 12 month meassure in DAX Commands and Tips

Slow performance of last 12 month meassure

FieF — Wed, 16 Feb 2022 10:09:15 GMT

Hi Guys

I have made this meassure to count that last running 12 months - I need help because the performance is very slow.

Dax:

VAR Maxdate = LASTDATE(DimDato[Dato] )

VAR Mindate = SAMEPERIODLASTYEAR(Maxdate)

VAR Result =

CALCULATE([Antal betalte donorer], FILTER(ALL(DimDato), DimDato[Dato] <= Maxdate && DimDato[Dato] > Mindate ) )

Return Result

It works as it should, but It Is really slow around 25000 Ms every click I make, from the performance analysis I get from Power BI:

// DAX Query
DEFINE
MEASURE 'FactBetalingslinjer'[Sidste 12 måneder antal betalte donorer] =
(/* USER DAX BEGIN */

VAR Maxdate = LASTDATE(DimDato[Dato] )
VAR Mindate = SAMEPERIODLASTYEAR(Maxdate)
VAR Result =
CALCULATE([Antal betalte donorer], FILTER(ALL(DimDato), DimDato[Dato] <= Maxdate && DimDato[Dato] > Mindate ) )
Return Result
/* USER DAX END */)

VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('DimDato'[Dato])),
AND(
AND('DimDato'[Dato] >= DATE(2021, 1, 1), 'DimDato'[Dato] < DATE(2022, 1, 1)),
'DimDato'[Dato] >= (DATE(2020, 9, 1) + TIME(0, 0, 1))
)
)

VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('DimDonortype'[Donorgruppe])),
AND(
'DimDonortype'[Donorgruppe] IN {"Faste støtter"},
'DimDonortype'[Donorgruppe] IN {"Faste støtter"}
)
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'DimDato'[MånedÅr],
'DimDato'[FørsteDatoMåned],
__DS0FilterTable,
__DS0FilterTable2,
"Sidste 12 måneder antal betalte donorer", 'FactBetalingslinjer'[Sidste 12 måneder antal betalte donorer]
)

VAR __DS0PrimaryWindowed =
TOPN(1001, __DS0Core, 'DimDato'[FørsteDatoMåned], 1, 'DimDato'[MånedÅr], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'DimDato'[FørsteDatoMåned], 'DimDato'[MånedÅr]

Re: Slow performance of last 12 month meassure

amitchandak — Wed, 16 Feb 2022 10:25:17 GMT

FieF , please try a measure like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

and see it perform better

Re: Slow performance of last 12 month meassure

FieF — Wed, 16 Feb 2022 10:43:08 GMT

Hi,

Thank you for your respond.

I already tried this and it do not work, it does not give me 12 month like I want. we have seasons in payment at different intervals, so this formular does not return what I want.

Re: Slow performance of last 12 month meassure

amitchandak — Wed, 16 Feb 2022 11:00:15 GMT

FieF , do you need something like

Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))

Re: Slow performance of last 12 month meassure

FieF — Mon, 21 Feb 2022 10:46:09 GMT

Hi Again

Thank you, the performance for this is as slow as the one I already have. I dont know why it performs that slow.

Re: Slow performance of last 12 month meassure

V-lianl-msft — Tue, 22 Feb 2022 06:56:22 GMT

Hi FieF ,

How about the performance of [Antal betalte donorer]?

Report performance is usually related to the size of pbix, the performance of local computer and the performance of DAX formula.

Please refer to these blogs:

https://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

https://maqsoftware.com/insights/dax-best-practices

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

Re: Slow performance of last 12 month meassure

FieF — Wed, 23 Feb 2022 09:00:06 GMT

That could make sence, it seems to be an issue with Antal betalte donorer, I now put in the dax formular for this:

CALCULATE(DISTINCTCOUNT(FactBetalingslinjer[DonorNummer]), USERELATIONSHIP(FactBetalingslinjer[DatoKeyBogfoeringsdato], DimDato[DatoKey]).

and my formular are now really long, do you have an idea to make it shortere and maybe better performing.

Sidste 12 måneder antal betalte donorer =

VAR Maxdate = LASTDATE(DimDato[Dato] )

VAR Mindate = SAMEPERIODLASTYEAR(Maxdate)

VAR Result =

CALCULATE(CALCULATE(DISTINCTCOUNT(FactBetalingslinjer[DonorNummer]), USERELATIONSHIP(FactBetalingslinjer[DatoKeyBogfoeringsdato], DimDato[DatoKey])), FILTER(ALL(DimDato), DimDato[Dato] <= Maxdate && DimDato[Dato] > Mindate ) )

Return Result