Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

 

 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

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

sanimesa
Post Prodigy
Post Prodigy

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
Not applicable

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

 

 

Anonymous
Not applicable

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.

@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
Not applicable

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

@Anonymous An example is in my earlier reply. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.