Dynamic Prior Period Month To Date

[tony393912]

Hello everyone,

I have a very interesting problem when trying to calculate the prior period month-to-date figure. Normally, I would just use a time-intelligence formula but my company has a couple unique things that makes this a very challenging task (for me at least):

1) Fiscal year is from July 1 to June 30

2) Revenue is very depedent on day of the week. Let's say if we were to compare MTD from 7/1/2022 to 7/10/2022, then we would compare it the period from 7/2/2021 to 7/11/2021 so that the day of the week matches. 

The data structure is very simple. There's a fact table with sales figures linking to a date table.

I was able to figure out the formula to calculate prior year daily revenue by doing the following:

1) Added a column in my Date table with this formula: 

DWY = WEEKDAY('Date'[Date])*1000000+WEEKNUM('Date'[Date])*10000+YEAR('Date'[Date])

2) Then I created a measure using the following:

Prior Period Daily Revenue  = CALCULATE([Daily Revenue],FILTER(ALL('Date'),'Date'[DWY] = MAX ('Date'[DWY])-1))

This actually works out nicely to calculate the prior period daily revenue.

Then, I attempted to do a Prior Period MTD by writing the following:

PY MTD Revenue =

VAR FirstPeriodLY = DATE ( YEAR( MAX('Date'[Date]))-1,1,1)
VAR LastPeriodPY = MAX ('Date'[Date])-1
RETURN
CALCULATE( [Daily Revenue],
DATESBETWEEN('Date'[Date],FirstPeriodLY,LastPeriodPY))

The formula doesn't work at all. I I just need to figure our how to set a variable so that it would give me the starting date and ending date but I couldn't figure out how to do it. Any help is much appreciated!

[amitchandak]

@tony393912 , same week day last year is 364 day behind

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

Normal

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

or

MTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(year(_max1), month(_max1), day(_max)-364)
var _min1 = eomonth(_max,-1)+1 ,
var _min = date(year(_min1), month(_min1), day(_min1)-364)
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))