Rolling Average Distinct count question

[thomsontam]

Hi,

I got a huge data set with 7m+ rows with the data like the following:

Company	Product	Country	Unit	Date
1	A	AU	1	1/07/2020
1	B	NZ	1	1/07/2020
2	A	NZ	1	1/07/2020
2	B	AU	1	1/07/2020
3	A	AU	1	1/07/2020
3	C	NZ	1	1/07/2020
4	B	AU	1	1/07/2020
5	C	AU	1	1/07/2020
6	A	AU	1	1/07/2020
6	B	AU	1	1/07/2020
6	C	AU	1	1/07/2020

I am able to create 90 Days running average on unit using the follow formula:

CALCULATE((Sum(table1[Unit]))/90,
DATESBETWEEN(table1[Date],
MAX(table1[Date]) - 89 ,
MAX(table1[Date]))

90 Days running average on customer count by

# of A  = 

Calculate(DistinctCount(Table1[Customer]),
FILTER(Table1,table1[Product]="A")
)

# of B = 

Calculate(DistinctCount(Table1[Customer]),
FILTER(Table1,table1[Product]="B")
)

# of A Avg 90D =
AVERAGEX(
DATESBETWEEN(table1[Date],
MAX(table1[Date]) - 89,
MAX(table1[Date])),table1[# of A]
)

# of B Avg 90D =
AVERAGEX(
DATESBETWEEN(table1[Date],
MAX(table1[Date]) - 89,
MAX(table1[Date])),table1[# of B]
)

A vs B Ratio 90D =
DIVIDE([# of A Avg 90D], [# of B Avg 90D])

All calculates are working on a small dataset but failed to load once data over 3m rows and took long time to refresh when change in filter.

Could you please give me some suggest how I should reformat the data ?

We need to do a lot of ratio analysis on product in difference country so I created a lot of individual measure for each product...

Thanks a lot!

[Greg_Deckler · Accepted solution]

@thomsontam I wrote a series of blog posts on DAX Performance Optimization

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

Have you used the Performance Analyzer? 

There is a series of best practices here for optimization:

https://maqsoftware.com/expertise/powerbi/dax-best-practices

There is great learning module available here:

https://docs.microsoft.com/en-us/learn/modules/optimize-model-power-bi/

Other than that, not sure I can assist. Didn't mean to link spam but performance optimization in DAX is a thick subject and would need your dataset to be specific!

[thomsontam]

After review my DAX from the suggestion in your link, found it was setup my filter incorrectly. 

Fix the filter and now only took 4 sec to recal rather then 4 mins.

Thanks!

[amitchandak]

@thomsontam , I am not sure it can give you a performance gain. But create a date table and join it with you table and do this calculation based on date table

try

Rolling 90 = CALCULATE(sum(table1[Unit]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-90,DAY))  //or 89

or

CALCULATE((Sum(table1[Unit]))/90,
DATESBETWEEN(Date[Date],
MAX(Date[Date]) - 89 ,
MAX(Date[Date]))

Or like This

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.