Forum Discussion

qthuynh's avatar
qthuynh
Frequent Visitor
8 years ago
Solved

Switch Formula Help (summarizecolumn?)

I have a table to pulls in real-time data. This is fine when mgt. wants to see real-time data, but not so great when they only want to view data up to the last month. I've tried a few switch statemen...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi qthuynh,

     

    Create the following measures:

     

    MTD_Calcution =
SUMX (
    Data;
    IF (
        MAX ( CurrentMonth[Current Month] ) = "YES";
        CALCULATE ( TOTALMTD ( SUM ( Data[Value] ); 'Calendar'[Date] ) );
        IF (
            MONTH ( TODAY () - DAY ( TODAY () ) )
                >= MONTH ( MAX ( 'Calendar'[Date] ) );
            CALCULATE ( TOTALMTD ( SUM ( Data[Value] ); 'Calendar'[Date] ) )
        )
    )
)
    Total MTD =
IF (
    HASONEFILTER ( 'Calendar'[Date] );
    [MTD_Calcution];
    SUMX ( Data; [MTD_Calcution] )
)

    Add the last measure to your table.

     

     

    Regards,

    MFelix

  • qthuynh's avatar
    qthuynh
    8 years ago

    Excellent solution MFelix. I went down a slightly different path.

     

    Step 1 - Added a new calculated column to my date table

         IsCurrentMonth = IF(MONTH(Dates_Table[Date]) = MONTH(TODAY()) && YEAR(Dates_Table[Date]) = YEAR(TODAY()),1,0)

     

    Step 2 - Created new measure to exclude current month

         Month excl = CALCULATE( SUM ( Data[Value] ), Dates_Table[IsCurrentMonth]=0 )

     

    Step 3 - Changed name of original measure to Month incl

         Month incl = SUM( Data[Value] )

     

    Step 4 - Created Switch Measure referencing Month excl & incl and added it to the table

         Month = SWITCH ( TRUE(),

                            VALUES ( 'Month View'[Month View] ) = "Yes", [Month incl],

                            VALUES ( 'Month View'[Month View] ) = "No", [Month excl],

                            0 )

qthuynh's avatar
qthuynh
Frequent Visitor
8 years ago

Felix,

 

Your formula works, but then it doesn't include total at the bottom. I modified it from TOTALMYD to TOTALYTD. Follow up question, I tried adding in BLANK(), but the formula tells me that there's too many arguments in the formula. Max is 3.

 

Is there another nested formula to show blanks in the months without values?

(e.g. the highlighted values in YTD Actual should be a blank cell like the "Actual" column)

 

MFelix's avatar
MFelix
Super User
8 years ago

Hi qthuynh,

 

Create the following measures:

 

MTD_Calcution =
SUMX (
    Data;
    IF (
        MAX ( CurrentMonth[Current Month] ) = "YES";
        CALCULATE ( TOTALMTD ( SUM ( Data[Value] ); 'Calendar'[Date] ) );
        IF (
            MONTH ( TODAY () - DAY ( TODAY () ) )
                >= MONTH ( MAX ( 'Calendar'[Date] ) );
            CALCULATE ( TOTALMTD ( SUM ( Data[Value] ); 'Calendar'[Date] ) )
        )
    )
)
Total MTD =
IF (
    HASONEFILTER ( 'Calendar'[Date] );
    [MTD_Calcution];
    SUMX ( Data; [MTD_Calcution] )
)

Add the last measure to your table.

 

 

Regards,

MFelix

  • qthuynh's avatar
    qthuynh
    Frequent Visitor
    8 years ago

    Excellent solution MFelix. I went down a slightly different path.

     

    Step 1 - Added a new calculated column to my date table

         IsCurrentMonth = IF(MONTH(Dates_Table[Date]) = MONTH(TODAY()) && YEAR(Dates_Table[Date]) = YEAR(TODAY()),1,0)

     

    Step 2 - Created new measure to exclude current month

         Month excl = CALCULATE( SUM ( Data[Value] ), Dates_Table[IsCurrentMonth]=0 )

     

    Step 3 - Changed name of original measure to Month incl

         Month incl = SUM( Data[Value] )

     

    Step 4 - Created Switch Measure referencing Month excl & incl and added it to the table

         Month = SWITCH ( TRUE(),

                            VALUES ( 'Month View'[Month View] ) = "Yes", [Month incl],

                            VALUES ( 'Month View'[Month View] ) = "No", [Month excl],

                            0 )