Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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
Not applicable

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

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
Not applicable

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

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.