Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?