switch columns based on time slicer

[Anonymous]

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

Do you have any solutions? Thanks!

[Anonymous · Accepted solution]

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

[daXtreme]

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]

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