Forum Discussion

brycewps's avatar
brycewps
Regular Visitor
3 years ago
Solved

Calculate Subtotals in a matrix when using if statement

Hi - I have a three data sources, sales table, forecast table, and calendar table and would like to create a measure that retrieves actual sales for days in the past and forecasted sales for today an...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi brycewps ,

     

    Please try:

    Act/Fcst Sls =
VAR _a =
    ADDCOLUMNS (
        'Calendar',
        "Value",
            IF (
                [Date] < TODAY(),
                CALCULATE (
                    SUM ( Scenario[Value] ),
                    FILTER (
                        'Scenario',
                        [Scenario] = "Actual"
                            && [Date] = EARLIER ( 'Calendar'[Date] )
                    )
                ),
                CALCULATE (
                    SUM ( 'Fcst Input'[Value] ),
                    FILTER (
                        'Fcst Input',
                        [Metric] = "Sales"
                            && [Date] = EARLIER ( 'Calendar'[Date] )
                    )
                )
            )
    )
RETURN
    SUMX ( _a, [Value] )

    Final output:

    Best Regards,

    Jianbo Li

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