Forum Discussion

Gabb's avatar
Gabb
Regular Visitor
3 years ago

Creating a calculate average 6 months fixed in a chart

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(     SUM( 'S...
Need Help
Gabb's avatar
Gabb
Regular Visitor
3 years ago

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)

 



  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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

    • Gabb's avatar
      Gabb
      Regular Visitor
      3 years ago

      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.