Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kingchad5
Helper I
Helper I

TOTALYTD Returning BLANK

I am having some trouble with a simple TOTALYTD formula.  I am trying to count the total projects YTD.  My table has a relation from the Submitted date of the project and a Date table.  I have tried most of the other examples from the forum with no luck.  

 


Proposals YTD = COUNT('Professional Services Quotes'[Project Name])

     Returns the total count of all projects


Proposals YTD = TOTALYTD(COUNT('Professional Services Quotes'[Project Name]),Dates[Date].[Date])

     Returns BLANK


Proposals YTD = TOTALYTD(COUNT('Professional Services Quotes'[Project Name]),Dates[Date].[Date], filter('Professional Services Quotes','Professional Services Quotes'[Submitted Date] < NOW()))

     Returns BLANK

 

I am also trying to do a comparison to Last Year to date with no luck as well.  I am guessing the fix for the YTD will solve my problem with the YTD LY calculation.  If anyone has any ideas it would greatly appriciate it.

 

Thanks 

1 ACCEPTED SOLUTION

@kingchad5

 

With the TOTALYTD or DATESYTD functions, year-to-date is defined relative to the maximum value of Dates[Date] in the filter context.

 

If the maximum value of Dates[Date] is in 2025 and you haven't applied any Date filters,  and your 'Professional Services Quotes' table doesn't extend to 2025, then TOTALYTD changes the filter on Dates[Date] to the range 1/1/2025-31/12/2025 (assuming you have the complete 2025 calendar year in Dates), which would give you a BLANK result.

 

Normally these functions are used in the context of days or months within a single year, and will return a YTD value within that year.


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

View solution in original post

11 REPLIES 11
Sean
Community Champion
Community Champion

Since you have a Date/Calendar table - DO NOT use the .[Date] notation - reference only Dates[Date] Smiley Happy

I get the same result, BLANK.  Could it have anything to do with the formatting of the columns?

 

Proposals2 YTD = TOTALYTD(COUNT('Professional Services Quotes'[Project Name]),'Dates'[Date]) 

 

Also, when I add a Year Slicer and select 2017 I get results.

 

8-2-2017 7-51-28 AM.jpg

 

@kingchad5

 

With the TOTALYTD or DATESYTD functions, year-to-date is defined relative to the maximum value of Dates[Date] in the filter context.

 

If the maximum value of Dates[Date] is in 2025 and you haven't applied any Date filters,  and your 'Professional Services Quotes' table doesn't extend to 2025, then TOTALYTD changes the filter on Dates[Date] to the range 1/1/2025-31/12/2025 (assuming you have the complete 2025 calendar year in Dates), which would give you a BLANK result.

 

Normally these functions are used in the context of days or months within a single year, and will return a YTD value within that year.


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

Hi, Ive filtered date in the formula, but it still returns BLANK. Do you know why?

YTD $ = TOTALYTD([Fixed Budget $], DateTable[Dates], DATESBETWEEN(Horizon[Date], MIN(Horizon[Date]), MAX(Horizon[Date])))
Expression 'Fixed Budget $' = SUM(Horizon[Fixed Budget USD])

@Anonymous Just saw your reply to this post 

Not sure if you have solved already.

 

The problem with your measure is that you are applying two different date filters (on different columns as well), which might have no intersection, resulting in a blank result for the measure.

 

Your original measure

YTD $ =
TOTALYTD (
    [Fixed Budget $],
    DateTable[Dates],
    DATESBETWEEN ( Horizon[Date], MIN ( Horizon[Date] ), MAX ( Horizon[Date] ) )
)

translates to

YTD $ =
CALCULATE (
    [Fixed Budget $],
    DATESYTD ( DateTable[Dates] ),
    DATESBETWEEN ( Horizon[Date], MIN ( Horizon[Date] ), MAX ( Horizon[Date] ) )
)

 (see https://dax.guide/totalytd/)

 

Could you describe the date filter that you are wanting to produce? Also, did you intend to filter both DatesTable[Dates] and Horizon[Date]?

 

Regards,

Owen


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

Thank you soooo much. Problem Solved.

Very Useful, Thanks a lot 

Great answer. Thank you. Exactly explains why my result was blank too.

 

My dates table extends many years beyond today... I’m hoping it’s a bit of future proofing.

what syntax do you recommend in that totaltyd function that can use the date table and yet make the results relative to today? ... and I guess not break the function if a page filter removes all future dates and back some months...

 

- David

but i have set the date with respect to min/max even then i am getting blank ytd

This did the trick.  I updated my Date table to reflect the MIN/MAX of dates in my data table.

 

Thanks for your help.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.