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 )

MFelix's avatar
MFelix
Super User
8 years ago

Hi qthuynh,

 

You just need to add a filter to your data using a calculate measure. I'm assuming you have a calendar table and a data table I made this mock up but should work also.

 

  • Create a table (Current Month) with:
    • YES
    • NO
  • Add the following measure
Total YTD =
IF (
    MAX ( CurrentMonth[Current Month] ) = "YES";
    CALCULATE ( TOTALMTD ( SUM ( Data[Value] ); 'Calendar'[Date] ) );
    CALCULATE (
        TOTALMTD ( SUM ( Data[Value] ); 'Calendar'[Date] );
        Data[Date]
            < TODAY () - DAY ( TODAY () )
    )
)

Use the measure you want the important part is the one in bold.

 

Final result will be like this:

 

 

Regards,

MFelix