Better Year to Date Total 10-31-2022 14:40 PM

[Greg_Deckler]

Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total, Better Average per Category, Better Weighted Average per Category, Better Filtered Value, and Better Sales from New Customers, this one starts to tackle some of the time intellignce-based quick measures, Year to Date Total.

Power BI's Year to Date Total quick measure returns something like this:

Value YTD = 
TOTALYTD(SUM('Table'[Value]), 'Dates'[Date])

Which may seem great until you try to use it with fiscal calendars and such. A better way:

Better YTD = 
    VAR __Date = MAX('Table'[Date])
    VAR __Year = MAX('Table'[Year])
    VAR __Table = FILTER(ALL('Table'),[Date] <= __Date && [Year] = __Year)
RETURN
    SUMX(__Table,[Value])

Watch the video!

eyJrIjoiOTAxMjFlNDYtNTgxMy00ZDRiLWI3NzMtZWI0NjI2MDQ2MjQ5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[pete_x_fox]

Greg, Love the video, super easy to follow but I can't seem to get the Better YTD total formula to filter my data which are located on a different table then the calendar master which has the dates.  I think my calendar table doesn't want to recognize my "year" column as a date because I'm limited to formatting it as either text or a whole number in my calendar table. When I try to change the data type to date it changes all the values to 1905 Any thoughts?