IF Statement for Months only returns value for first month

[Anonymous]

Hello everybody,

I want to create an IF Statement that returns the YTD average sales for each month. The syntax in the Formula is correct but I always only get a value returned for 2023/01. My Goal would be to have a bar for each month that shows the ytd average sales, so for Feb is is the sales of Jan + Feb divided by 2 etc. 

Any idea why I only get a value returned for 2023/01?

AVG YTD Sales Actual 2022 = 

IF(
    MIN('IMTT: MASTER TABLE'[Year_Month]) = "2023/01",
    VAR YTD_Sales =
    CALCULATE(SUM('IMTT: MASTER TABLE'[ACT Sales 2022]),
    'IMTT: MASTER TABLE'[Year_Month] = "2023/01") 
    VAR YTD_AVG_Sales = DIVIDE(YTD_Sales,1)
    RETURN YTD_AVG_Sales,
    

    IF(
    MIN('IMTT: MASTER TABLE'[Year_Month]) = "2023/02",
    VAR YTD_Sales =
    CALCULATE(SUM('IMTT: MASTER TABLE'[ACT Sales 2022]),
    'IMTT: MASTER TABLE'[Year_Month] = "2023/01",
    'IMTT: MASTER TABLE'[Year_Month] = "2023/02") 
    VAR YTD_AVG_Sales = DIVIDE(YTD_Sales,2)
    RETURN YTD_AVG_Sales,

    IF(
    MIN('IMTT: MASTER TABLE'[Year_Month]) = "2023/03",
    VAR YTD_Sales =
    CALCULATE(SUM('IMTT: MASTER TABLE'[ACT Sales 2022]),
    'IMTT: MASTER TABLE'[Year_Month] = "2023/01",
    'IMTT: MASTER TABLE'[Year_Month] = "2023/02",
    'IMTT: MASTER TABLE'[Year_Month] = "2023/03") 
    VAR YTD_AVG_Sales = DIVIDE(YTD_Sales,3)
    RETURN YTD_AVG_Sales, ...)

[v-rzhou-msft]

Hi @Anonymous ,

I suggest you to create a Calendar table to help you calcualte the average.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2023, 12, 31 ) ),
    "Year_Month", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonth Sort",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Year", YEAR ( [Date] )
)

Data model:

Measure:

AVG = 
VAR _YTD =
    CALCULATE (
        SUM ( 'IMTT: MASTER TABLE'[ACT Sales 2022] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[YearMonth Sort] <= MAX ( 'Calendar'[YearMonth Sort] )
                && 'Calendar'[Year] = MAX ( 'Calendar'[Year] )
        )
    )
VAR _MONTH =
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[Year_Month] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[YearMonth Sort] <= MAX ( 'Calendar'[YearMonth Sort] )
                && 'Calendar'[Year] = MAX ( 'Calendar'[Year] )
        )
    )
RETURN
    DIVIDE ( _YTD, _MONTH )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

First of all, thanks a lot! This really helps.

One Problem: I have one more level of detail (Plant) in the table where I also habe the ACT sales 2022 and therefore it is not working. How could I make it work with this additional detail level?

[Anonymous]

Hi @v-rzhou-msft , are you able to help me make it work with the additional level of Plant?

[Anonymous]

Hi @v-rzhou-msft , are you able to help me make it work with the additional level of Plant?