EnterpriseDNA has some really good videos on custom financial calendars. I’ve found the ones where the SELECTEDVALUE function is used as Variables in determining Previous Year to Date (PYTD) with custom fiscal calendars. Sales YTD and Sales PY are working correctly with the fiscal 4-4-5 calendar. However, I’ve been unable to translate those teachings into a valid “Sales PYTD - Fiscal445” measure.
Enterprise DNA (Melissa, in particular) has been exposed to this PBIX file recently in producing Increase/Decrease/No Change counts in YTD and PYTD comparisons based on a standard calendar. The objective now is to produce the same measures (PYTD, in particular) with a custom 4-4-5 fiscal calendar.
Specifically per the attached, my measure of “Sales PYTD - Fiscal445” produces a valid total for fiscal year 2020 (and all Sales reps). However, the individual rows in the Matrix visualization per Product ID do not display the values which sum to the total… some individual values display, some do not. Similarly, when a Sales Rep is selected in the Slicer, the total is also incorrect.
With these Slicer selections (Fiscal Year = 2020, Sales Rep = Paige Turner) - this Orders table record should be included in the “Sales PYTD - Fiscal445” calculation:
Order ID: CA-2015-1578125400
Product ID: OFF-BI-10000285
Ship Date: 3/26/2019
It is not included, and I haven’t stumbled into the “Sales PYTD - Fiscal445” logic which is correct.
PBIX and its Excel data source are attached.
I appreciate the help.
PowerBI_Sample_Dataset.xlsx (46.7 KB)