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