Better Year to Date Total

Greg_Deckler — Mon, 31 Oct 2022 21:40:30 GMT

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

Re: Better Year to Date Total

pete_x_fox — Thu, 09 Nov 2023 17:56:42 GMT

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?

topic Better Year to Date Total in Quick Measures Gallery

Better Year to Date Total

Re: Better Year to Date Total