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

Replacing <year end date> in TOTALYTD Function with TODAY()-Rolling Year to Date Measure

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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))

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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))

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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.

  1. A date serial number (e.g. created by the DATE function) is not allowed.
  2. A measure or variable is not allowed, even if it returns a string, like FORMAT ( TODAY(), "mm/dd" )

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 )
    )

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

 

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

  • take on any fiscal year end => measure FYE_Month draws month value (1...12) from a params table (where I store entities' name, currency, as well as fiscal year end)
  • work on any year => incorporate calendar table
  • work on any month (irrespective of 28, 29, 30 or 31 day months...) => Start with the fiscal year start instead of end
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))

 

Anonymous
Not applicable

Could you please share what FYE_Month does?

Anonymous
Not applicable

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.

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.