Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table (Timeslips) that contains data on workers' timesheets (Timekeeper, DateWorked, Hours, etc.). The DateWorked field is related to the Date table.
I developed the following measure for the 12-month running sum of hours (running from the first day of the previous month in the prior year to the last day of the previous month in the current year):
12-Month Moving Sum Hours = CALCULATE(Sum(Timeslips[Hours]),DATESBETWEEN(Table_BasicCalendarUS[DateKey],SAMEPERIODLASTYEAR(STARTOFMONTH(Table_BasicCalendarUS[DateKey])),PREVIOUSDAY(STARTOFMONTH(Table_BasicCalendarUS[DateKey]))),ALL(Timeslips[Hours]))
This measure works fine and properly shows the 12-month running totals for each timekeeper for each month.
I would like to have a visual showing only the current 12-month running total of Hours for each Timekeeper, but I am lost figuring out how to filter the CALCULATE function to accomplish this result. I tried a number of variations on the FILTER function, e.g., FILTER(Table_BasicCalendarUS[DateKey]=TODAY, but nothing has worked so far.
Any suggestions would be appreciated.
Solved! Go to Solution.
Another way is use date function to get the Year, Month and Day part from TODAY(). Year part minus one, then concatenate each part to get the same day in last year and convert it into date type. Please refer to formula below:
12-Month Moving Sum Hours = VAR TodayInLastYear = DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(YEAR(TODAY())-1,"-"),MONTH(TODAY())),CONCATENATE("-",DAY(TODAY())))) RETURN CALCULATE(Sum(Timeslips[Hours]), FILTER(ALL(Table_BasicCalendarUS),Table_BasicCalendarUS[DateKey]>TodayInLastYear && Table_BasicCalendarUS[DateKey]<TODAY()), ALL(Timeslips[Hours]))
Regards,
Does your calendar table stop at the current month, or does it over run? If the latter, you could consider using power query to keep the calendar up to date. Here is an article I wrote last year http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
once your our calendar is fixed, you can use LASTDATE to find the latest date and hence month. Alternatively, you could use lastdate on your fact table to the same outcome.
Matt:
Thanks very much for this suggestion. I created one of your custom Date tables. Pretty nifty.
I'll give this suggestion a try.
Another way is use date function to get the Year, Month and Day part from TODAY(). Year part minus one, then concatenate each part to get the same day in last year and convert it into date type. Please refer to formula below:
12-Month Moving Sum Hours = VAR TodayInLastYear = DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(YEAR(TODAY())-1,"-"),MONTH(TODAY())),CONCATENATE("-",DAY(TODAY())))) RETURN CALCULATE(Sum(Timeslips[Hours]), FILTER(ALL(Table_BasicCalendarUS),Table_BasicCalendarUS[DateKey]>TodayInLastYear && Table_BasicCalendarUS[DateKey]<TODAY()), ALL(Timeslips[Hours]))
Regards,
I'll give that a try. Thanks for the suggestion.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |