Average growth just for the selected time period (Problem with DATEADD)

[scoobydoo]

Hello everyone,

I am building a dynamic visual that shows the monthly or quarterly sales within a selected time period. However, I have encountered an issue with the calculated “Average Monthly Growth” measure, which contradicts the trend line of the columns.

The trend is positive, but the calculated Averager Monthly Growth (marked in red below) is negative.

The DAX:

Selected Metric MoM% = 
VAR __PREV_MONTH = CALCULATE([Selected Metric ], DATEADD('Datetable'[Date], -1, MONTH))
RETURN
	DIVIDE([Selected Metric ] - __PREV_MONTH, __PREV_MONTH)

Selected Metric MoM% Average = 
VAR MonthlyGrowthTable =
    SUMMARIZE(
        FILTER(Datetable, NOT(ISBLANK([Selected Metric]))),
        Datetable[MonthYear],
        "Monthly Growth", [Selected Metric MoM%]
    )
RETURN
AVERAGEX(MonthlyGrowthTable, [Monthly Growth])

he reason behind this contradiction is that my measure also includes June to calculate the monthly growth for July. This growth from June to July was -10.5%, therefore the Average growth is negative. I cannot leave it like this, so I would like to show only the monthly growth within this time frame.

I am looking for a solution that will allow me to get a result for DATEADD only when the result is within the current Date filter context. If this doesn't make sense are any other solutions to get the desired result? Thank you very much in advance!

P.S.: If it’s important, the measure has to automatically switch from “monthly growth” to “quarterly growth”, depending on the selection in the “View Time By”-Slicer. At the moment I used to the “hard” MonthYear column, but if there is a way to make it already more flexible, any hint would be highly appreciated.

[v-shex-msft]

Hi @scoobydoo ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[scoobydoo]

I finally solved the problem myself over the weekend. If anyone is interested in the final code of the main affected measures, I copied it below. Please note that it already contains the options to change the metric (revenue, profit, margin) and the time period (month, quarter) which is why it is quite long.

As I'm still very new to Power BI, I'm sure there are more elegant ways to solve this, but at least it works and I learned a lot.

Avg Growth Rate ACT = 
VAR SelectedTimePeriod = MAX( Trend_TimePeriods[Time periods] )  
 
RETURN 
    IF(
        SelectedTimePeriod = "Quarter",
        AVERAGEX(
            ALLSELECTED( Datetable[QuarterYear] ),
            IF(
                NOT ISBLANK( [Growth Rate ACT] ),
                [Growth Rate ACT]
            )
            ), AVERAGEX(
            ALLSELECTED( Datetable[MonthYear] ),
            IF(
                NOT ISBLANK( [Growth Rate ACT] ),
                [Growth Rate ACT]
            )
        )
    )
	
	
Growth Rate ACT = 
VAR SelectedMeasure = MAX( Trend_Measure[Trend_Measure] )
VAR SelectedTimePeriod = MAX( Trend_TimePeriods[Time periods] )
VAR CurrentDate =
    IF(
        SelectedTimePeriod = "Quarter",
        MAX( Datetable[QuarterCode] ),
        MAX( Datetable[MonthCode] )
    )
VAR EarliestDate =
    IF(
        SelectedTimePeriod = "Quarter",
        CALCULATE(
            MIN( Datetable[QuarterCode] ),
            ALLSELECTED( Datetable[QuarterYear] )
            ), CALCULATE(
            MIN( Datetable[MonthCode] ),
            ALLSELECTED( Datetable[MonthYear] )
        )
    )

VAR PreviousMonthSales =
    IF(
        CurrentDate > EarliestDate,
        IF(
            SelectedTimePeriod = "Quarter",
            CALCULATE(
                SWITCH(
                    SelectedMeasure,
                    "Sales", [Sales 21-ACT],
                    "Gross Profit", [Gross Profit 21-ACT],
                    "Gross Margin", [Gross Margin 21-ACT]
                ),
                DATEADD( Datetable[Date], - 1, QUARTER ),
                ALLSELECTED( Datetable[QuarterYear] )
            )
            ,
            
            CALCULATE(
                SWITCH(
                    SelectedMeasure,
                    "Sales", [Sales 21-ACT],
                    "Gross Profit", [Gross Profit 21-ACT],
                    "Gross Margin", [Gross Margin 21-ACT]
                ),
                DATEADD( Datetable[Date], - 1, MONTH ),
                ALLSELECTED( Datetable[MonthYear] )
            )
        )
        )
RETURN
    IF(
        NOT ISBLANK( PreviousMonthSales )
            && NOT ISBLANK(
                SWITCH(
                    SelectedMeasure,
                    "Sales", [Sales 21-ACT],
                    "Gross Profit", [Gross Profit 21-ACT],
                    "Gross Margin", [Gross Margin 21-ACT]
                )
            ),
        DIVIDE(
            SWITCH(
                SelectedMeasure,
                "Sales", [Sales 21-ACT],
                "Gross Profit", [Gross Profit 21-ACT],
                "Gross Margin", [Gross Margin 21-ACT]
            )
                - PreviousMonthSales,
            PreviousMonthSales,
            BLANK( )
        )
    )	

 P.S.: I couldn't share the dataset as it is very huge and I couldn't find a way to randomize the datasets (which are confidential and imported in several Power Queries).