cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

3 REPLIES 3
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]))

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

Resolver I

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.

Announcements