Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |