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.
Hi @kkiege, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Thank you, Ankit. I’ve attempted to apply the two links into a “Sales PYTD v2 - Fiscal445” measure, along with a new FISC_YR_DAY_NBR column, but have yet to achieve the desired result.
My updated PBIX file is attached. My presumption remains to use a variable for max ( Orders[Ship Date] ) from the fact table rather than from the Dates dim table which may or may not be the issue. In the end, I’m still seeking the valid DAX for “Sales Previous YTD based on the 4-4-5 fiscal calendar” measure.
Just checking if anyone has suggestions on getting my “Sales PYTD - Fiscal445” or “Sales PYTD v2 - Fiscal445” measure to work properly in the PBIX provided on 4/20.
Indeed, the “PYTD - Fiscal 4-4-5” measure is so very close to being accurate, but I can detail examples of where both row level and totals are not accurate. I believe you will be proven correct in that a context issue exists, but my skills to “step through” a given measure to pinpoint the problem haven’t been fruitful.
I’m attaching the updated PBIX, its source file, a snapshot of the Fiscal 4-4-5 calendar table with pertinent dates, a worksheet of the two Sales reps and their respective sales orders, and a “Further Details” document to illustrate the problematic results with the ‘Sales PYTD - Fiscal 445’ measure.
As mentioned, ‘Sales PYTD v2 - Fiscal 445’ was a different attempt to achieve the expected result, but triggers a different set of unexpected results.
Hi @kkieger, It’s great to know that you are making progress with your query. Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate. For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum
I’m still hoping for a response which “fixes” my PYTD - Fiscal 4-4-5 measure. The extended thread is at Sam’s request, and I provided attachments in my previous reply which detail the unexpected results.
