Forum Discussion
Date Filter Dax
Hello! I created a dax formula that is the following:
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.
7 Replies
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"
)Change the order of the checks, list them the other way round. SWITCH will exit after the first match.
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)RETURNSWITCH(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")No, you will only ever get a scalar value (single result) out of a SWITCH statement.
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.
