Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 work.   Thanks in advance for your help.
Show and Tell
Tips and Tricks
Tutorial Requests
  • sanimesa's avatar
    sanimesa
    6 years ago

    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")

     

     

9 Replies

  • sanimesa's avatar
    sanimesa
    Post Prodigy
    6 years ago

    Can you please post the fomula you are using?  A common issue with date dimension with dates in fact table is if dates in fact table is defined as date-time, it may not match. You can try checking that by creating a table and a simple date dimension filter, whether it is filtering the fact table at all in the first place. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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")
      • sanimesa's avatar
        sanimesa
        Post Prodigy
        6 years ago

        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")

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    sanimesa Thank you that worked!  I will mark as accepted.

    • sanimesa's avatar
      sanimesa
      Post Prodigy
      6 years ago

      Anonymous Glad it worked for you! Thanks for accepting it as solution!