Same Period Last Year for a 52 week Financial Calendar

[Anonymous]

Hi,

I am trying to run a comparison of sales on a day by day basis versus last year but using a Fiscal Calendar that is based on 52 weeks and 4-4-5 week quarters. Therefore FY16 started on 4th April 2015 and FY17 started on 2nd April 2016.   

I've set up a Measure to calculate Last Year's Sales as below and also associated Cumulative measures. This works reasonably well as I can filter down to this Fiscal Year (2nd April 2016 onwards), but its doing a natural calendar compare so the first day of this fiscal compares to day 363 of last fiscal and clearly sales go up as the quarter/year end pressures kick in.

How can I create an equivalent SamePeriodLastYear function but to 364 days ago not to the same date last year?

Measures:

SalesWonLY = CALCULATE([WonSales],SAMEPERIODLASTYEAR('DATEDIMENSION'[DateKey])) and then cumulative Measures as:

CumulativeWonSales = CALCULATE(
      [WonSales],
       FILTER(
       ALLSELECTED (DATEDIMENSION), DATEDIMENSION[DateKey] <= MAX (DATEDIMENSION[DateKey])
       )
)

and

CumulativeWonSalesLY = CALCULATE(
      [SalesWonLY],
       FILTER(
       ALLSELECTED (DATEDIMENSION), DATEDIMENSION[DateKey] <= MAX (DATEDIMENSION[DateKey])
       )
)

Thanks,

Simon

[mmanwaring]

Hi Simon

I find these measures a challenge as well.

Maybe look at capturing the current day of your fiscal year in your filter and then moving the fiscal year back ?

thanks

Mike

[Anonymous]

Hi @mmanwaring Mike,

WonSales = SUMX('SALESFORCE EXTRACT',IF('SALESFORCE EXTRACT'[Forecast Category]="Closed",'SALESFORCE EXTRACT'[Offering Final Value (converted)],0))

The DateDimension table has a DateKey which is the normal date, ranging from max to min based on the decision date in the Salesforce Extract table.

I've set up columns within that table that has a day number within Financial Year, thinking that may help! So e.g. 02/04/16 has 1 in the Day of FY column.

Thanks,

Simon

[mmanwaring · Accepted solution]

Yes so if you know day 1 of your ficscal year then you can use that and to go back previous year using filter. let me know how you get on.

I do not use 445 but here is an example of my measure to capture last year to date cumulative. My Sort column just identifies month number.

2015/2016 YTD =
VAR
SortV = CALCULATE(MAX(DimDates[Sort]),DimDates[Fyear]="2016/2017")
VAR
DayV = CALCULATE(DAY(LASTDATE(DimDates[Date])),DimDates[Fyear]="2016/2017")
RETURN
CALCULATE([YTD],
FILTER(ALL(DimDates),
DimDates[Sort] <= SortV &&
DimDates[Day] <= DayV &&
DimDates[Fyear]="2015/2016"))

[Anonymous]

@mmanwaring - Thanks Mike. I'll give that whirl and see if I can get it to work! Will report back!

[Anonymous]

@mmanwaring - Thankyou...

Slightly adapted, but this seems to do the job!

CumulativeSalesWonLFY =
VAR
SortY = CALCULATE(MAX(DATEDIMENSION[FinancialYearNumber]))
VAR
DayV = CALCULATE(MAX(DATEDIMENSION[DayofFYNumber]))
RETURN
CALCULATE([WonSales],FILTER(ALL(DATEDIMENSION),DATEDIMENSION[DayofFYNumber]<= DayV && DATEDIMENSION[FinancialYearNumber] = SortY-1))