Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |