Forum Discussion
Date Filter
@ links to members, content Hi I need assistance writing a DAX formula that will produce this Period filter. I have my relationship with my dimension and date table however my formula will not ...
AnonymousDid you create a relationship between your new calculated table and fact table?
A simpler and crude appoach would be to create a calculated column in your date dimension table called Period which will use a switch statement to spit out a period value. A crude vesion below:
Period = SWITCH(TRUE(), TODAY() - 'Date Table'[Date] <= 7, "Last 7 Days", TODAY() - 'Date Table'[Date] <=30, "Last 30 Days", "All period")
This is the formula I tried to use is there an easier way?
DatePeriod =
UNION (
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-07+1,today()) ), 'Date'[Date]),"Period","Last 07 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-14+1,today()) ), 'Date'[Date]),"Period","Last 14 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-30+1,today()) ), 'Date'[Date]),"Period","Last 30 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-90+1,today()) ), 'Date'[Date]),"Period","Last 90 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall")
AnonymousDid you create a relationship between your new calculated table and fact table?
A simpler and crude appoach would be to create a calculated column in your date dimension table called Period which will use a switch statement to spit out a period value. A crude vesion below:
Period =
SWITCH(TRUE(),
TODAY() - 'Date Table'[Date] <= 7, "Last 7 Days",
TODAY() - 'Date Table'[Date] <=30, "Last 30 Days",
"All period")