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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Anonymous
Not applicable

switch columns based on time slicer

Hi, I am new to use DAX, and I want to create  a calculation to calculate metrics,

the rule is that when date is prior to present then select actual, if later than now then select forecast, and sum the values, and should also consider the date slicer

for example:

start_month_silcer=202206 end_month_silcer=2022010, and current date is 202208,

so the value is (7+3+2 + 5+3), 202206 to 202208 is 7+3+2 , and 202208 to 202210 is 5+3

null_0-1659505187310.png

Do you have any solutions? Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous,

You can use the measure formula with variable and summarize function to achieve your requirement:

formual =
VAR _start =
    MIN ( Calendr[Date] )
VAR _end =
    MAX ( Calendr[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Date] >= _start && [Date] <= _end ),
        [Date],
        [Actual],
        [Forecast],
        "Amount", IF ( [Date] <= TODAY (), [Actual], [Forecast] )
    )
RETURN
    SUMX ( summary, [Amount] )

Regards,

Moonlight

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Anonymous,

You can use the measure formula with variable and summarize function to achieve your requirement:

formual =
VAR _start =
    MIN ( Calendr[Date] )
VAR _end =
    MAX ( Calendr[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Date] >= _start && [Date] <= _end ),
        [Date],
        [Actual],
        [Forecast],
        "Amount", IF ( [Date] <= TODAY (), [Actual], [Forecast] )
    )
RETURN
    SUMX ( summary, [Amount] )

Regards,

Moonlight

Hi @Anonymous 

 

Please do not use SUMMARIZE in measures to calculate a field in its body.

 

This function is buggy and can quasi-randomly return wrong results and Microsoft has refused to fix it. It should only be used for grouping rows and that's the only safe scenario where you can use it.

 

For more details, please consult this article: All the secrets of SUMMARIZE - SQLBI

 

The article also discusses ways to replace this capability.

daXtreme
Solution Sage
Solution Sage

Here's Alberto Ferrari explaining how to do it: https://youtu.be/DKgF-5QHY68

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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