Forum Discussion

tony393912's avatar
tony393912
New Member
3 years ago

Dynamic Prior Period Month To Date

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!

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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))