Creating a calculate average 6 months fixed in a chart

Question

Hey all, I have this chart:I want to add 2 different 6 months' averages for the sell-out and shipped.Both the seel out and shipped are 2 DAX.Shipped = CALCULATE(&nbsp; &nbsp; SUM( 'Shipped May-Aug 21'[Quantity]),&nbsp; &nbsp; &nbsp; &nbsp; FILTER('Shipped May-Aug 21',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Shipped May-Aug 21'[Key Figure] = "Shipped")&nbsp; &nbsp; &nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Shipped May-Aug 21'[Product Family] IN {"BIZ", "PRO", "Engage", "EVOLVE", "EVOLVE2", "SPEAK", "SPEAK2", "PANACAST", "STEALTH UC"})&nbsp; (The file&nbsp;'Shipped May-Aug 21' contains all the queries with simalar files till oct 22)Sell out = CALCULATE(&nbsp; &nbsp; SUM('factView Channel Transactions'[Quantity])&nbsp; &nbsp; ,&nbsp; &nbsp; 'dimView Product'[Product Family Name] IN {"_SPECIF PRODUCTS_", "STEALTH UC"}&nbsp; &nbsp; ,&nbsp; &nbsp; 'dimView Product'[Product Division Name] = "X" &amp;&amp;&nbsp; &nbsp; 'dimView Product'[Product Legal Entity Code] = "X"&nbsp; &nbsp; ,&nbsp; &nbsp; 'dimView Calendar'[MonthsDistanceFromCurrentMonth] &lt; 0)I tried several different way, but I keep having strange numbers or the saame number as the total shipped or sell out.Can you suggest me a way to do the average?

v-easonf-msft · Answer

Hi，&nbsp;Gabb&nbsp;
I'm not sure what you mean by "calculate average 6 months fixed in a chart".
Are you referring to calculating the rolling 6 months average of&nbsp;sell-out and shipped?
Please check if the solutions in the following threads could help.
Rolling 12 Months Average in DAX&nbsp;
How to calculate 3 months and 6 months average&nbsp;
Best Regards,Community Support Team _ Eason
&nbsp;
&nbsp;
&nbsp;

v-easonf-msft · Answer

Hi,&nbsp;Gabb&nbsp;
I think you can add a new measure, using an if statement to replace the average value under the "forecasted" section with a blank value.
&nbsp;
Best Regards,Community Support Team _ Eason

gabb · Answer

Basically, I wrote the formula like this:6 months Shipped Rolling Average = IF(&nbsp; &nbsp; ISFILTERED('dimView Calendar'[Calendar Date]),&nbsp; &nbsp; ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),&nbsp; &nbsp; VAR __LAST_DATE = ENDOFMONTH('dimView Calendar'[Calendar Date].[Date])&nbsp; &nbsp; VAR __DATE_PERIOD =&nbsp; &nbsp; &nbsp; &nbsp; DATESBETWEEN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dimView Calendar'[Calendar Date].[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; STARTOFMONTH(DATEADD(__LAST_DATE, -6, MONTH)),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; __LAST_DATE&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATETABLE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('dimView Calendar'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dimView Calendar'[Calendar Date].[Year],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dimView Calendar'[Calendar Date].[QuarterNo],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dimView Calendar'[Calendar Date].[Quarter],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dimView Calendar'[Calendar Date].[Month],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dimView Calendar'[Calendar Date].[MonthNo]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; __DATE_PERIOD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Shipped],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL('dimView Calendar'[Calendar Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ))and it works.However, I don´t want the "forecasted" part. Ho can I delete it? (The future outcomes)&nbsp;

gabb · Answer

Do you have any ideas on how I could write it?

I tried different IF statement but I was trying to connect it with a column I have on my dimView CALENDAR "DISTANCE FROM CURRENT MONTH".

IF "dimView Call[Distancefromcurrentmonth] <=0,
Give me all the values
IF "dimView Clal[Distancefromcurrentmonth] > 0,
"Blank"

But I cannot link the mesure with that.

Forum Discussion

Creating a calculate average 6 months fixed in a chart

4 Replies

Related content

DAX Measure for Ranking the valid rows and calculating the average rating

Calculating Weighted Average

Calculating average 12 month average

Average calculation

Average Calculation