Date Filter Dax

[nataliesmiy1357]

Hello!  I created a dax formula that is the following:

Date Range Filter =

VAR TodayDate = TODAY()

VAR Past3Months = EDATE(TodayDate, -3)

VAR Past6Months = EDATE(TodayDate, -6)

VAR Past12Months = EDATE(TodayDate, -12)

RETURN

SWITCH(

    TRUE(),

    'Date'[Date] >= Past3Months, "Past 3 Months",

    'Date'[Date] >= Past6Months, "Past 6 Months",

    'Date'[Date] >= Past12Months, "Past 12 Months",

    "All Data"

)

But I want to make sure that the past 6 months inlcludes that data, but also the past 3 months (so it's kinda cumulative)

How is this possible?

[Anonymous · Accepted solution]

Thank you lbendlin and Shravan133 

Hi, @nataliesmiy1357 

When you use >=, your switch function should look like this:

SWITCH(
    TRUE(),
    'Date'[Date] >= Past12Months, "Past12Months",
    'Date'[Date] >= Past6Months, "Past 6 Months",
    'Date'[Date] >= Past3Months, "Past3Months",
    "All Data"
)

Or when you use <=, your switch function should look like this:

SWITCH(
    TRUE(),
    'Date'[Date] <= Past3Months, "Past3Months",
    'Date'[Date] <= Past6Months, "Past 6 Months",
    'Date'[Date] <= Past12Months, "Past12Months",
    "All Data"
)

Let me use the following example to show you why:

Measure 2 = SWITCH(TRUE(),
 SUM('Table'[Value])<=190,"A",
 SUM('Table'[Value])<=150,"B",
 SUM('Table'[Value])<=90,"C"
)

In my case 94 is less than 190 and 150, and its correct level should be B. However, since the switch ends the judgment when the first condition is met, then the line corresponding to 94 is judged to be A.

When I change it to something like this, I get the correct output.

Measure 2 = SWITCH(TRUE(),
 SUM('Table'[Value])<=90,"A",
 SUM('Table'[Value])<=150,"B",
 SUM('Table'[Value])<=190,"C"
)

I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

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

[lbendlin]

Change the order of the checks, list them the other way round. SWITCH will exit after the first match.

[nataliesmiy1357]

When I switch them - I am still only getting one grouping.  I want to make sure when I do:

3 months data = 3 months

6 months = 3 months and 3 more months

12 months = 6 months and 6 more months.

Date Range Filter =

VAR TodayDate = TODAY()

VAR Past3Months = EDATE(TodayDate, -3)

VAR Past6Months = EDATE(TodayDate, -6)

VAR Past12Months = EDATE(TodayDate, -12)

RETURN

SWITCH(

    TRUE(),

    'OR Report'[DATE].[Date] <= Past12Months, "Last 12 Months",

    'OR Report'[DATE].[Date] <= Past6Months, "Last 06 Months",

    'OR Report'[DATE].[Date] <= Past3Months, "Last 03 Months",

    "All Data"

)

[lbendlin]

No, you will only ever get a scalar value (single result) out of a SWITCH statement.

[nataliesmiy1357]

So how would I code it without a switch?

[lbendlin]

You would add these flags to your calendar table.

[Shravan133]

try this: 

Date Range Filter =
VAR TodayDate = TODAY()
VAR Past3Months = EDATE(TodayDate, -3)
VAR Past6Months = EDATE(TodayDate, -6)
VAR Past12Months = EDATE(TodayDate, -12)
RETURN
SWITCH(
TRUE(),
'Date'[Date] >= Past3Months, "Past 3 Months",
'Date'[Date] >= Past6Months && 'Date'[Date] < Past3Months, "Past 6 Months (including Past 3 Months)",
'Date'[Date] >= Past12Months && 'Date'[Date] < Past6Months, "Past 12 Months (including Past 6 and 3 Months)",
"All Data"
)