cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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.
Fill.jpgtables.jpg
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Post Prodigy
Post Prodigy

Re: Date Filter

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

 

 

View solution in original post

9 REPLIES 9
Highlighted
Post Prodigy
Post Prodigy

Re: Date Filter

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. 

Highlighted
Helper II
Helper II

Re: Date Filter

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")
Highlighted
Post Prodigy
Post Prodigy

Re: Date Filter

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

 

 

View solution in original post

Highlighted
Helper II
Helper II

Re: Date Filter

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.

Highlighted
Post Prodigy
Post Prodigy

Re: Date Filter

@Evester 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. 

Highlighted
Helper II
Helper II

Re: Date Filter

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

Highlighted
Post Prodigy
Post Prodigy

Re: Date Filter

@Evester An example is in my earlier reply. 

Highlighted
Helper II
Helper II

Re: Date Filter

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

Highlighted
Post Prodigy
Post Prodigy

Re: Date Filter

@Evester Glad it worked for you! Thanks for accepting it as solution! 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors