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,

MojoGene's avatar
MojoGene
Post Patron
10 years ago

Matt:

 

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'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.