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,

MattAllington's avatar
MattAllington
Community Champion
10 years ago

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. 

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.