Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
qthuynh
Frequent Visitor

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 statements, but the end result does not give me what I need.

 

I'm looking for a DAX formula to show the total value based on the selected criteria of "Include Current Month".

 

E.g. if current month is selected then the total value would be X. If current month is "NO", then the total value would be X

 

Current Month.JPG

2 ACCEPTED SOLUTIONS

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.

 

MTDTOTAL.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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 )

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

You should be able to use relative date filters.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

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:

 

current_month.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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)

 

Current Month.JPG

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.

 

MTDTOTAL.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 )

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.