Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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:
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:
@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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |