Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gabb
Regular Visitor

Creating a calculate average 6 months fixed in a chart

Hey all, I have this chart:

Gabb_0-1669715866178.png

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(
    SUM( 'Shipped May-Aug 21'[Quantity]),
        FILTER('Shipped May-Aug 21',
            'Shipped May-Aug 21'[Key Figure] = "Shipped")
        ,
            'Shipped May-Aug 21'[Product Family] IN {"BIZ", "PRO", "Engage", "EVOLVE", "EVOLVE2", "SPEAK", "SPEAK2", "PANACAST", "STEALTH UC"}
)  (The file 'Shipped May-Aug 21' contains all the queries with simalar files till oct 22)

Sell out =
CALCULATE(
    SUM('factView Channel Transactions'[Quantity])
    ,
    'dimView Product'[Product Family Name] IN {"_SPECIF PRODUCTS_", "STEALTH UC"}
    ,
    'dimView Product'[Product Division Name] = "X" &&
    'dimView Product'[Product Legal Entity Code] = "X"
    ,
    'dimView Calendar'[MonthsDistanceFromCurrentMonth] < 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?
4 REPLIES 4
Gabb
Regular Visitor

Basically, I wrote the formula like this:

6 months Shipped Rolling Average =
IF(
    ISFILTERED('dimView Calendar'[Calendar Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __LAST_DATE = ENDOFMONTH('dimView Calendar'[Calendar Date].[Date])
    VAR __DATE_PERIOD =
        DATESBETWEEN(
            'dimView Calendar'[Calendar Date].[Date],
            STARTOFMONTH(DATEADD(__LAST_DATE, -6, MONTH)),
            __LAST_DATE
        )
    RETURN
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    VALUES('dimView Calendar'),
                    'dimView Calendar'[Calendar Date].[Year],
                    'dimView Calendar'[Calendar Date].[QuarterNo],
                    'dimView Calendar'[Calendar Date].[Quarter],
                    'dimView Calendar'[Calendar Date].[Month],
                    'dimView Calendar'[Calendar Date].[MonthNo]
                ),
                __DATE_PERIOD
            ),
            CALCULATE(
                [Shipped],
                ALL('dimView Calendar'[Calendar Date])
            )
        )
)
and it works.

However, I don´t want the "forecasted" part. Ho can I delete it? (The future outcomes)

Gabb_0-1669802671410.png

 



Hi, @Gabb 

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.

 

Best Regards,
Community Support Team _ Eason

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.

v-easonf-msft
Community Support
Community Support

Hi, @Gabb 

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 sell-out and shipped?

Please check if the solutions in the following threads could help.

Rolling 12 Months Average in DAX 

How to calculate 3 months and 6 months average 

Best Regards,
Community Support Team _ Eason

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.