cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sjcaldwell475
Frequent Visitor

Same Period Last Year for a 52 week Financial Calendar

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

1 ACCEPTED SOLUTION

Accepted Solutions
mmanwaring Resolver I
Resolver I

Re: Same Period Last Year for a 52 week Financial Calendar

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

View solution in original post

5 REPLIES 5
mmanwaring Resolver I
Resolver I

Re: Same Period Last Year for a 52 week Financial Calendar

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

sjcaldwell475
Frequent Visitor

Re: Same Period Last Year for a 52 week Financial Calendar

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 Resolver I
Resolver I

Re: Same Period Last Year for a 52 week Financial Calendar

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

View solution in original post

sjcaldwell475
Frequent Visitor

Re: Same Period Last Year for a 52 week Financial Calendar

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

sjcaldwell475
Frequent Visitor

Re: Same Period Last Year for a 52 week Financial Calendar

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors