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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

how do you create a calculated column in date table.   Are you saying create this calculated column in the dimension table or the date table?  Either way I dont know how to create the calculated column.

sanimesa's avatar
sanimesa
Post Prodigy
6 years ago

Anonymous You will create this calculated column on yout Date Dimension table (which I presume is called 'Date Time'). 

It is simple to create a calculated column - you right click on table name and select New Column from thee menu. It will open up an area on top where you enter a DAX expression. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    sanimesa  Ok sounds easy but what is the expession I need to write for the new column?

    • sanimesa's avatar
      sanimesa
      Post Prodigy
      6 years ago

      Anonymous An example is in my earlier reply.