Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |