Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"))
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
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"))
@mmanwaring - Thanks Mike. I'll give that whirl and see if I can get it to work! Will report back!
@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))
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |