Month to Date calculations looking at the same time period, same business days count, over two years

[darkhorse13]

Hi all,

Here is the business case: say I want to look at revenue summed up from 9/1/16 thru 12/3116. Then, I want to look at the period with the SAME NUMBER OF BUSINESS DAYS as the time range above for 2015. I have struggled with a solution since SAMEPERIODLASTYEAR or PARALLELPERIOD will not work on its own since I have to take into account Holidays. For example, in the 2016 time frame, there was an additional holiday, the Friday after Thanksgiving (that the company counted as a holiday and not a business day for 2016) but was a business day for 2015. I have thought of looking at the MAX (count of business days) from 2016 to = 2015 but not sure how I can show this. I envision a slicer where I can select a date range, and that slicer will control the current year date range selected and show the revenue, but then in a table, also show the previous years' revenue over the same # of business days (defined by my company). Any and all insight would be great! I have scoured the community forums looking for a similar solution and have not found one yet 😞 Thanks!

[MattAllington · Accepted solution]

Here is some reading for you. 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

http://exceleratorbi.com.au/dax-time-intelligence-beginners/

My idea Is to create a calendar with a Day-ID column starting at 1 and incrementing by 1 for every day (never restarts).  Also have another WorkDay-ID column starting at 1 and incrementing by 1 for every workday (never restarts). You can then write custom time intelligence using the ID column to find the Day-ID this year and the same day prior year (current Day-ID minus 365). You can write measures to determine the Start and End workday ID in your current filter context and hence the number of workdays. You can use the Day-iD to find the anchor date (either start or end date 365 days ago) and then the number of work days to set the other end (you have to anchor either the start or end date given the number of days can vary). 

I haven't tested it, but at is what I suggest you try.