Forum Discussion
Quick measure: rolling average - ERROR Power BI-provided date hierarchy / primary date column
Dear all,
my data model doesn't contain any valid date column. That's why I tried to convert an invalide column to a valid one using a formula that was proposed in this forum. At a first view, it seemed to work. However, when I tried to use the created date column in a quick measure, I got the following error message:
Please find in the following the steps I have taken
Formula to convert invalide date in a valid date:
Result
->
However, I got the following error message ...
Do you know why it's still not running?
Many thanks for your help. Kind regards, Mathias
podma In general, for Time Intelligence DAX functions to work you need to have Auto date/time intelligence turned on. You also need a separate Date table where you right-click the table and choose Mark as Date Table and then you set the date column that you want to use in your TI calculations to the primary date column for the date table.
If you want to avoid all that nonsense, you can use Better Rolling Average. I linked the video but here is the Quick Measure Gallery entry: (2) Better Rolling Average - Microsoft Fabric Community
The code is actually much shorter and simpler than what you get back from using the quick measure in Power BI Desktop. Below I'll try to adapt the code to your model using your measure as an example:
Better Rolling Average = VAR __EndDate = MAX('Demand + PVA'[ValidDate]) VAR __3MonthsAgo = EOMONTH(__EndDate, -3) VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1) VAR __Table = SUMMARIZE( FILTER(ALL('Demand + PVA'),[ValidDate]>=__StartDate && [ValidDate]<=__EndDate), 'Demand + PVA'[Month], "__Value",SUM('Demand + PVA'[Qty]) ) RETURN AVERAGEX(__Table,[__Value])You may have to add a column called "Month" using MONTH([ValidDate])
3 Replies
- Greg_Deckler
Community Champion
podma Do you have automatic date hierarchies turned on? Did you flag the table as a date table? If you are frustrated by DAX's TI functions ( we all are ), you can do these calculations without them. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Also,
Greg_Deckler: many thanks for your reply.
It seems to me that the column is flagged as a "date" (please see in the following). Is that good or bad?
I also checked if automatic date hierarchies were turned on. It was. I also turned it off without any effect:
I agree on your feeling that things (at least in this case) are much more easier in Excel. In Excel I would go for the formula fx = Average() and then select the cells I want to be considered in my moving average; then copy it and I'm done. That wouldn' cost me a minute ;-). I checked another time with my superior what exactly should be reflected in the moving average. Please see in the following:
I started checking the additional information material you shared with me. However, for a beginner who started his DAX journey only some weeks ago, it still looks quite complex.
Have a good day. Kind regards, Mathias
- Greg_Deckler
podma In general, for Time Intelligence DAX functions to work you need to have Auto date/time intelligence turned on. You also need a separate Date table where you right-click the table and choose Mark as Date Table and then you set the date column that you want to use in your TI calculations to the primary date column for the date table.
If you want to avoid all that nonsense, you can use Better Rolling Average. I linked the video but here is the Quick Measure Gallery entry: (2) Better Rolling Average - Microsoft Fabric Community
The code is actually much shorter and simpler than what you get back from using the quick measure in Power BI Desktop. Below I'll try to adapt the code to your model using your measure as an example:
Better Rolling Average = VAR __EndDate = MAX('Demand + PVA'[ValidDate]) VAR __3MonthsAgo = EOMONTH(__EndDate, -3) VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1) VAR __Table = SUMMARIZE( FILTER(ALL('Demand + PVA'),[ValidDate]>=__StartDate && [ValidDate]<=__EndDate), 'Demand + PVA'[Month], "__Value",SUM('Demand + PVA'[Qty]) ) RETURN AVERAGEX(__Table,[__Value])You may have to add a column called "Month" using MONTH([ValidDate])
