Date hierarchy problem - Only Power BI-provided data hierarchies are supported

[Anonymous]

I am trying to calculate a monthly sales moving average but the rolling average time intelligence function only support Power BI-provided data hierarchies. What does "Power BI-provided data hierarchies" mean? How do I convert the current date hierarchies to the Powe BI supported ones? Thank you!

[v-juanli-msft]

Hi @Anonymous 

Is the "Dates[date]" column of type text or type date?

Does the "Date" table and "Transaction Line" table have a relationship?

You could refer to this article to learn how to create DAX for Moving average.

statistical aggregation functions, such as average, variance, and standard deviation

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I did a sanity check it seems not correct.

The daily sales qty was over 3 items over the past one month but the moving average qty is less than 1...not sure what went wrong? 

[Anonymous]

the "Dates[date]" column is type date.

Yes, the transaction line is daily retail transaction base on date.