Creating LY Sales measure with custom fiscal calendar

[TheLeadingEdge]

We have the traditional BI table with all of our detail sales and orders.   We use a custom Fiscal Calendar that starts in August, but also has its own 4-5-4 weekly date buckets.  These are our custom Fiscal periods (All 12).  When I select the fiscal period I want, it brings back all of the data perfectly for that Fiscal Period, but within that data set, I want to add LY Fiscal Month comp, which is not the traditional getdate from last year, its based on a set of dates from LY fiscal period.  We want specific days, not the -364, etc.

In the measure I create, I can get the exact value I want but hardcoding the LYStartFiscal and LYEndFiscal dates, but I don't want that.  I want it dynamic to choose the MAX and MIN LYFiscal Dates from the dataset that is showing.  How can I achieve that?  I have tried so many attempts and its frustrating.  The data that shows, can only have 1 LYStartFiscal and 1 LYEndFiscal date.  That is why I assumed MIN/MAX is the way to go to snag those 2 dates, and somehow get them in the Measure below, but it seems to not work.

**below, this gets me the result I want when i drop it on the current 2023 data in my dataset I chose, but want this below to change based on the fiscal periond I select.

LY Written =

CALCULATE

(

SUM('Delivered_Historical'[Price]),

DATESBETWEEN('Delivered_Historical'[OrderDate],            

            DATE(2022,10,24),   <----how do I get MIN LYStartDate from the dataset   - it displays on dataset, so it does know it

            DATE(2022,11,27)   <---- how do I get MAX LYEndDate from the dataset

            ),            

'Delivered_Historical'[OrderType] IN {"X", "Y","Z"}

)

this is a screen shot from the dataset.  everything is perfect, I just can't access these 2 columns above in the Measure.  

Maybe I am overcomplicating this, but would like some guidance here on best practices with my custom dates

Thanks in advance

[audreygerred]

Hello! I have found the best calendar for handling fiscal years and fiscal weeks if the calendar available through SQLBI. If you use that table you would configure it to be fiscal weekly following a 454, then set that table to be the date table and the yoy at that point is striaght forward, just as if you were using a traditional calendar. Reference Date Table in DAX and Power BI - SQLBI

Because this calendar is built to handle this you would just do LY = CALCULATE([YourMeasure, SAMEPERIODLASTYEAR('DateTable'[Date]))

If you are filtered to period 1 in this scenario, it would filter TY to weeks 1-4 and LY to weeks 1-4 (regardless of what the dates are because it is working on the defined weeks that create a 'month'/period). I use this calendar table every day and it is a dream.

[TheLeadingEdge]

Thank you for the response.  I have a custom Date Calendar that is Joined to the OrderDate.  So I have all the structure.  SAMEPERIODLASTYEAR is not producing the correct timeframe.  It is comparing the actula date, we need the Calendar day.  This year is a back example because of Leap Year, but when I apply the SAMEPERIODLASTYEAR to it, I am off by a Day.  

Is there a way to pluck out the MAX sdate and edate from the dateset and place it in that measure above?

[audreygerred]

In the reference calendar I provided, the day of year is a field so you would use that to filter instead of calendar date. Generally, when a fiscal weekly calendar is used peope are comparing week 1 to week 1, period 2 (i.e. weeks 5-9) to period 2 and since it is at the weekly level, the dates themselves do not matter.... unless in your visual you are trying to put the actual day in - then it will break down and not work as expected. I would suggest using day of week as your lowest granularity - that way it will compare Monday of week 1 this year to Monday of week 1 last year and so on and so forth. When you use a fiscal weekly calendar you should use fiscal year, fiscal period, fiscal week, and day of week so that it is easily comparable to the exact same timeframe and reference of a year prior. Hope this helps!

[TheLeadingEdge]

I have my own 5 year custom corp fiscal calender built out.  On every row (CalDate) lets call it, I have a handful of date columns that go with it, including the Last Years Calendar Day matching date (based on leap year or not leap year) and also the "LY FiscalDate".  I currently choose the FiscalDates from a drop down and I am able to pull that data perfectly into my data table.  Now, how can I write a Measure that sums up data base don the LYFiscalDates value?  Basically I feel like its a Sub Query, but its not working.   

Does that help getting me closer to an answer?   I feel like I am missing something so simple here.....All I want is the below, but it has to understand what to filter on based on the selection already made in the report

With that said, I currently Join the CalDate in my custom calendar to the WrittenDate in my main table of sales.  So when the report runs, it shows FiscalDates from dropdown, but the measure I create cannot find the LYFiscalDates rows and sum them properly.  The filters are applied in the measure.

How can I achieve this?