Need help enhancing DAX to include an "After Today" logic statement.

[krichmond]

Details are provided below as to what we currently have in place. What I need to do is enhance the "Actual Prmeium With Time Designator" field so that if the date is in the future "Today + 1 Day" it pulls from a field called "Projected Premium". The "Projected Premium" field sits in a table called "mv_perfex".

I have a working column called "90 Day Date Designator" with the following formula:

IF(DATEDIFF(mv_perfex[startdate],TODAY(),DAY)>0&&DATEDIFF(mv_perfex[startdate],TODAY(),DAY)<=90,"True","False")

I have another working column called "180 Day Date Designator" with the following formula:

IF(DATEDIFF(mv_perfex[startdate],TODAY(),DAY)>90&&DATEDIFF(mv_perfex[startdate],TODAY(),DAY)<=180,"True","False")

 

I than have a field called "Actual Premium With Time Designator" that leverages these two other fields. This field has the following formula:

SWITCH(TRUE(),

[90 Day Date Designator]="True",[Projected Average Buy]*[Projected Paid Responders],

[180 Day Date Designator]="True",mv_perfex[Prem @100%],

[180 Day Date Designator]="False",mv_perfex[Act_Prem])

[Anonymous]

Hi @krichmond ,

Here I suggest you to try this measure.

Actual Premium With Time Designator =
VAR _DATEDIFF =
    DATEDIFF ( mv_perfex[startdate], TODAY (), DAY )
RETURN
    SWITCH (
        TRUE (),
        _DATEDIFF = 1, [Projected Premium],
        _DATEDIFF > 1
            && _DATEDIFF <= 90, [Projected Average Buy] * [Projected Paid Responders],
        _DATEDIFF > 90
            && _DATEDIFF <= 180, mv_perfex[Prem @100%],
        mv_perfex[Act_Prem]
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@krichmond , Based on what I got. A new column

SWITCH(TRUE(),

mv_perfex[startdate] > today() , [Projected Premium]
[90 Day Date Designator]="True",[Projected Average Buy]*[Projected Paid Responders],
[180 Day Date Designator]="True",mv_perfex[Prem @100%],
[180 Day Date Designator]="False",mv_perfex[Act_Prem])