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
Tihannah
Resolver I
Resolver I

PY Dax with 2 Date Filters

Trying to figure out how to create PY dax that takes into account two date filters. My model is set up with a Calender dimesion table and my fact table has both Create and Business date. With help from the forum, I was able to create a USERELATIONSHIP for them to work in the filters. However, my PY dax which is currently:

 

Sales PY = CALCULATE('Table'[Sales],SAMEPERIODLASTYEAR('Calendar'[date])) returns the same number as CY.

Sales PY = CALCULATE('Table'[Sales],SAMEPERIODLASTYEAR('Table'[Create date])) return PY, but cannot be filtered on Business date or data goes blank.

Sales PY = CALCULATE('Table'[Sales],SAMEPERIODLASTYEAR('Table'[Business date])) returns blank data

 

The PY calculation needs to be able to filter on both dates. Can this be done?  Having no luck finding a solution.  Thanks.

 

1 ACCEPTED SOLUTION
Tihannah
Resolver I
Resolver I

Just in case someone else needs it, a coworker found the solution and it was surprisingly a lot easier than I thought. He used both Create Date and Business Date Calendar dimension tables.  The dax is written:

 

PY Sales = CALCULATE(SUM('Table'[Total Sales]),SAMEPERIODLASTYEAR('Business Date'[Date]),SAMEPERIODLASTYEAR('Create Date'[ Date]))

 

View solution in original post

3 REPLIES 3
Tihannah
Resolver I
Resolver I

Just in case someone else needs it, a coworker found the solution and it was surprisingly a lot easier than I thought. He used both Create Date and Business Date Calendar dimension tables.  The dax is written:

 

PY Sales = CALCULATE(SUM('Table'[Total Sales]),SAMEPERIODLASTYEAR('Business Date'[Date]),SAMEPERIODLASTYEAR('Create Date'[ Date]))

 

amitchandak
Super User
Super User

@Tihannah , You have to try like

 

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),SAMEPERIODLASTYEAR('Date'[Date]))

 

make sure you use date table marked as date table in visual and slicer

This is not returning anything for me?  I have my Calendar table joined on the fact table on Business Date. I also have a relationship to Created Date and Cancel date, but these relationships are inactive. I got this help in a previous thread to make sure all the measures could be filtered on these 3 dates.  

 

My Current Year dax:

CY_Sales = CALCULATE(CALCULATE(SUM('Table'[Total Saless]),USERELATIONSHIP('Calendar'[Cal_Dt],'Table'[Business Date])),USERELATIONSHIP('Calendar'[Cal_Dt],'Table'[Cancel Date]))
 
This works fine. The PY dax above is returning blank. The example I am using to validate is looking at the month of January (Created Date), sales booked for 2/14/23 (Business Date) and trying to compare to these same date ranges for 2022.

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.