Forum Discussion
Rolling Average Distinct count question
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!
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!
3 Replies
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!
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!
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
