Custom DAX Formula to switch between wtd mtd qtd and ytd

[mpw]

Hi - I receive an error message "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." here is the formula below, please help me correct this issue.

Sales Analysis =

VAR SelectedPeriod = SELECTEDVALUE(TimePeriods[Period])

VAR CurrentDate = MAX('EL Month Conversion'[Date])

VAR Start_Week = CALCULATE(

    MIN('EL Month Conversion'[Date]),

    FILTER(

        'EL Month Conversion',

        'EL Month Conversion'[FiscalWeekOfYear] = CALCULATE(

            MAX('EL Month Conversion'[FiscalWeekOfYear]),

            'EL Month Conversion'[Date] = CurrentDate

        )

    )

)

VAR Start_Month = CALCULATE(

    MIN('EL Month Conversion'[Date]),

    FILTER(

        'EL Month Conversion',

        'EL Month Conversion'[FiscalMonthNumber] = CALCULATE(

            MAX('EL Month Conversion'[FiscalMonthNumber]),

            'EL Month Conversion'[Date] = CurrentDate

        )

    )

)

VAR Start_Quarter = CALCULATE(

    MIN('EL Month Conversion'[Date]),

    FILTER(

        'EL Month Conversion',

        'EL Month Conversion'[FiscalQuarterYear] = CALCULATE(

            MAX('EL Month Conversion'[FiscalQuarterYear]),

            'EL Month Conversion'[Date] = CurrentDate

        )

    )

)

VAR Start_Year = CALCULATE(

    MIN('EL Month Conversion'[Date]),

    FILTER(

        'EL Month Conversion',

        'EL Month Conversion'[FiscalYear] = CALCULATE(

            MAX('EL Month Conversion'[FiscalYear]),

            'EL Month Conversion'[Date] = CurrentDate

        )

    )

)

VAR TY_Value = SWITCH(

    TRUE(),

    SelectedPeriod = "WTD", CALCULATE(SUM('NA Sales w LY'[TY]), 'EL Month Conversion'[Date] >= Start_Week),

    SelectedPeriod = "MTD", CALCULATE(SUM('NA Sales w LY'[TY]), 'EL Month Conversion'[Date] >= Start_Month),

    SelectedPeriod = "QTD", CALCULATE(SUM('NA Sales w LY'[TY]), 'EL Month Conversion'[Date] >= Start_Quarter),

    SelectedPeriod = "YTD", CALCULATE(SUM('NA Sales w LY'[TY]), 'EL Month Conversion'[Date] >= Start_Year)

)

VAR LY_Value = SWITCH(

    TRUE(),

    SelectedPeriod = "WTD", CALCULATE(SUM('NA Sales w LY'[LY]), 'EL Month Conversion'[Date] >= Start_Week),

    SelectedPeriod = "MTD", CALCULATE(SUM('NA Sales w LY'[LY]), 'EL Month Conversion'[Date] >= Start_Month),

    SelectedPeriod = "QTD", CALCULATE(SUM('NA Sales w LY'[LY]), 'EL Month Conversion'[Date] >= Start_Quarter),

    SelectedPeriod = "YTD", CALCULATE(SUM('NA Sales w LY'[LY]), 'EL Month Conversion'[Date] >= Start_Year)

)

VAR PL_Value = SWITCH(

    TRUE(),

    SelectedPeriod = "WTD", CALCULATE(SUM('NA Sales w LY'[PL]), 'EL Month Conversion'[Date] >= Start_Week),

    SelectedPeriod = "MTD", CALCULATE(SUM('NA Sales w LY'[PL]), 'EL Month Conversion'[Date] >= Start_Month),

    SelectedPeriod = "QTD", CALCULATE(SUM('NA Sales w LY'[PL]), 'EL Month Conversion'[Date] >= Start_Quarter),

    SelectedPeriod = "YTD", CALCULATE(SUM('NA Sales w LY'[PL]), 'EL Month Conversion'[Date] >= Start_Year)

)

VAR LE_Value = SWITCH(

    TRUE(),

    SelectedPeriod = "WTD", CALCULATE(SUM('FY LE'[LE]), 'FY LE'[Retailer Week] >= Start_Week),

    SelectedPeriod = "MTD", CALCULATE(SUM('FY LE'[LE]), 'FY LE'[Retailer Week] >= Start_Month),

    SelectedPeriod = "QTD", CALCULATE(SUM('FY LE'[LE]), 'FY LE'[Retailer Week] >= Start_Quarter),

    SelectedPeriod = "YTD", CALCULATE(SUM('FY LE'[LE]), 'FY LE'[Retailer Week] >= Start_Year)

)

RETURN

    ADDCOLUMNS(

        ROW(

            "TY", TY_Value,

            "LY", LY_Value,

            "PL", PL_Value,

            "LE", LE_Value

        ),

        "Var TY vs LY", TY_Value - LY_Value,

        "Var TY vs PL", TY_Value - PL_Value,

        "Var TY vs LE", TY_Value - LE_Value

    )

[Anonymous]

Hi, @mpw 

Can you provide example files and what output you expect? This will better help you solve the problem. Please remove any sensitive data in advance.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

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

[mpw]

Thank you - see attached.

EL Month Conversion: date table

NA Sales w LY: sample data, TY(this year), LY(last year), PL(plan)

FY LE: sample data LE(latest estimate)

I would like to use this custom date table and create a switch that toggles wtd, mtd, qtd, ytd

[mpw]

also - here is a screenshot of my relationships

[lbendlin]

RETURN
    ADDCOLUMNS(
        ROW(
            "TY", TY_Value,
            "LY", LY_Value,
            "PL", PL_Value,
            "LE", LE_Value
        ),
        "Var TY vs LY", TY_Value - LY_Value,
        "Var TY vs PL", TY_Value - PL_Value,
        "Var TY vs LE", TY_Value - LE_Value
    )

You are returning a table. That is not possible for measures or calculated columns.