cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

Accepted Solutions
Community Support
Community Support

Hi @JeffatHero,

 

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
Community Support
Community Support

Hi @JeffatHero,

 

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

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.

@JeffatHero

 

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!

Connect on Twitter
Connect on LinkedIn

 

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

 

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.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors