cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

2 ACCEPTED SOLUTIONS
Super User

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] )
)```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

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 )

5 REPLIES 5
Super User

Hi,

You should be able to use relative date filters.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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
```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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

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)

Super User

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] )
)```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

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 )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors