Forum Discussion
Filter for Current Value?
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.
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,
4 Replies
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,
