Forum Discussion

MojoGene's avatar
MojoGene
Post Patron
10 years ago
Solved

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 f...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    MojoGene

     

    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,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

MojoGene

 

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's avatar
MojoGene
Post Patron
10 years ago

I'll give that a try. Thanks for the suggestion.