Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'd like to update this measure:
PageviewsYTD = TOTALYTD(SUM(FactSCRAMAnalytics[Pageviews]),DimDate[Date],All('DimDate'),"12/31")
by replacing the year end date with a function that returns today's date. My intent is to create a rolling YTD calculation of Pageviews. Would this be done by using the TODAY() function or by pulling the most recent date from my DimDate table?
Example DAX I'm working from:
TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])
Thanks,
Jeff
Solved! Go to Solution.
Hi @Anonymous,
You can try to use below formula to calculate the end date of current year.
EndDate = var temp=TODAY() return DATE(YEAR(MAX([Date])),MONTH(temp),DAY(temp))
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can try to use below formula to calculate the end date of current year.
EndDate = var temp=TODAY() return DATE(YEAR(MAX([Date])),MONTH(temp),DAY(temp))
Regards,
Xiaoxin Sheng
How do I apply the EndDate calculation to my YTD calculation? When I replace the date in the formula with the calculation I get and error.
This:
PageviewsYTD = TOTALYTD(SUM(FactSCRAMAnalytics[Pageviews]),DimDate[Date],All('DimDate'),[EndDate])
Gives me and error:
A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
The YTD calculation was previously written as:
PageviewsYTD = TOTALYTD(SUM(FactSCRAMAnalytics[Pageviews]),DimDate[Date],All('DimDate'),"12/31")
Thank you.
@Anonymous
I have played around with the TOTALYTD and DATESYTD functions, and unfortunately these functions steadfastly refuse to accept anything remotely 'dynamic' as the year-end date argument, and insist on a string literal.
I think the only solution is to create a custom measure that replicates the TOTALYTD/DATESYTD logic.
Here is an illustration of how the measure could be defined (quite verbose with a lot of variables, so could possibly be slimmed down!):
PageViews YTD Rolling = VAR TodayVar = TODAY () VAR YearEndMonth = MONTH ( TodayVar ) VAR YearEndDay = DAY ( TodayVar ) VAR MaxDate = MAX ( DimDate[Date] ) VAR MaxDateYear = YEAR ( MaxDate ) VAR YearEndThisYear = DATE ( MaxDateYear, YearEndMonth, YearEndDay ) VAR YearEndLastYear = DATE ( MaxDateYear - 1, YearEndMonth, YearEndDay ) VAR YearEndSelected = IF ( YearEndThisYear < MaxDate, YearEndThisYear, YearEndLastYear ) RETURN CALCULATE ( SUM ( FactSCRAMAnalytics[Pageviews] ), FILTER ( ALL ( DimDate[Date] ), AND ( DimDate[Date] > YearEndSelected, DimDate[Date] <= MaxDate ) ), // ALL ( DimDate) is only required if relationships with DimDate are not on the Date column ALL ( DimDate ) )
Thank you Owen for this illuminating comment. Tried to accomplish exactly that - have a data lake of financial data from multiple companies with multiple fiscal years and was about to tear my hairs out as YTD would just not work. Once I understood the limitation (sloppy programming by MSFT!) did a custom function. Just posting it here should there be s/body else running into the issue and look for a solution. Set up my YTD measure to
YTD = VAR EndDate = max('Calendar'[Date]) VAR StartDate = IF ( Date(year(EndDate),[FYE_Month]+1,1) < EndDate, Date(year(EndDate),[FYE_Month]+1,1), Date(year(EndDate)-1,[FYE_Month]+1,1) ) RETURN CALCULATE([Total],DATESBETWEEN('Calendar'[Date],StartDate,EndDate))
Could you please share what FYE_Month does?
FYE_Month stands for Fiscal Year End Month and an integer in the range [1,12]. If a company has Jan 31 as its financial year (e.g. as Salesforce does), FYE_Month is set to 1, March 31 would be 3 etc.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |