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 and future days.  

 

The formula I'm using works at the individual day level, but the subtotal for a timeframe ignore actuals and subtotals the forecast for all days within the selected timeframe.  How can I modify my formula to arrive at the correct subtotal of $10

 

Act/Fcst Sls =
var dummy = sum('Calendar'[Date])
return (
if( dummy < TODAY(), CALCULATE([Sls],Scenario[Scenario]="Actual"), CALCULATE(sum('Fcst Input'[Value]),'Fcst Input'[Metric]="Sales")))
 
Output
DateAct SlsFcst SlsAct/Fsct Sls
1/8/2023$1$2$1
1/9/2023$1$2$1
1/10/2023$1$2$1
1/11/2023$1$2$1
1/12/2023 $2$2
1/13/2023 $2$2
1/14/2023 $2$2
Total$4$14$14
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.

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    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.