Forum Discussion
MojoGene
Post Patron
Post PatronFilter 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 f...
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,
MojoGenePost Patron
Post PatronMatt:
Thanks very much for this suggestion. I created one of your custom Date tables. Pretty nifty.
I'll give this suggestion a try.
v-sihou-msft
Microsoft Employee
Microsoft Employee
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,
- MojoGene
I'll give that a try. Thanks for the suggestion.