PYTD Measure with Custom Fiscal (4-4-5) Calendar

Hi,

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.

Best regards,
Kevin

Sample DNA Dataset for PYTD with Custom Fiscal Calendar.pbix (197.8 KB)

PowerBI_Sample_Dataset.xlsx (46.7 KB)

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.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Are there any further details I can provide at this point?

Hi @kkieger

Please check if below links are helpful.


Thanks
Ankit J

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.

Kevin

Sample DNA Dataset for PYTD with Custom Fiscal Calendar.pbix (221.1 KB)

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.

Sorry for the delay,

Have you gone through this tutorial?

Combined with this tutorial

These go through this scenario and how to solved it in Power BI

Sam

It looks like you have a bit which is good

To me the main issue here is likely the context of the calculation in the table and how it aligns with your formula you’ve written

I can’t seem to replicate your example above.

Can you highlight using the same context below where you’re seeing the actual problem?

At a quick glance I can’t intuitively see any issue with totals etc or any numbers. So will need better direction.

Thanks
Sam

Thanks,.

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.

Thank you for the extra effort.

Kevin

PowerBI_Sample_Dataset.xlsx (46.0 KB)
Sample DNA Dataset for PYTD with Custom Fiscal Calendar.pbix (234.6 KB)
Enterprise Calendar Analysis.xlsx (51.4 KB)
PowerBI_Sample_Dataset Analysis.xlsx (46.6 KB)
PYTD Measure - Further Details.docx (252.2 KB)

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.