Forum Discussion

aflintdepm's avatar
aflintdepm
Icon for Helper III rankHelper III
3 years ago
Solved

Week To Date Gauge Calculated at EOD

I have a daily report that counts up for the entire week.  On that report, I have a guage that indicates the % of the week completed on the day of the report.  So, end of day Monday is 20%, Tuesday is 40%, etc. until Friday, which is 100%.

 

The problem I have is that the report comes out in the morning for the prior day (Monday's numbers on Tuesday).  Because of this, it shows 40% of the week completed on Tuesday morning when I want it to show 20%.

 

Is there a way to calculate on prior day for this?

 

Here is the measure I'm using to give each day 20% value

 

WeekCompletion =
VAR CurrentDate = TODAY()
VAR StartOfWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR EndOfWeek = StartOfWeek + 4
VAR DaysInWeek = 5
VAR CompletedDays = MIN(CurrentDate, EndOfWeek) - StartOfWeek + 1
RETURN
    CompletedDays / DaysInWeek
Tips and Tricks
  • aflintdepm's avatar
    aflintdepm
    3 years ago

    amitchandak Thank you for the reply.  I don't want to apply this to any other value.  I just want a % of the week completed.  How do I calculate that?  I don't need average or sum, I just need to know that, on Wednesday, 40% of the week (Mon and Tues) is complete.

     

    EDIT:

    I figured it out.  I just subtracted 1 from my CurrentDate in my CompletedDays variable

     

    WTD Yesterday =
    VAR CurrentDate = TODAY()
    VAR StartOfWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
    VAR EndOfWeek = StartOfWeek + 4
    VAR DaysInWeek = 5
    VAR CompletedDays = MIN(CurrentDate-1, EndOfWeek) - StartOfWeek + 1
    RETURN
        CompletedDays / DaysInWeek

     

    Thank you

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    aflintdepm , You try measure like

     

    WTD till yesterday =
    var _st = today() +-1*WEEKDAY(today(),2)+1
    var _end =today()+ 7-1*WEEKDAY(today(),2) -1
    return
    CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter

     

    or

    //net is a measure

    WTD Yesterday =
    var _max = today() -1
    var _min = _max -WEEKDAY(_max,2) +1 //Monday week start
    return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    for Avg

     

    WTD Yesterday =
    var _max = today() -1
    var _min = _max -WEEKDAY(_max,2) +1 //Monday week start
    return CALCULATE(Averagex(Values('Date'[Date]),  [Net]), FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    • aflintdepm's avatar
      aflintdepm
      Icon for Helper III rankHelper III
      3 years ago

      amitchandak Thank you for the reply.  I don't want to apply this to any other value.  I just want a % of the week completed.  How do I calculate that?  I don't need average or sum, I just need to know that, on Wednesday, 40% of the week (Mon and Tues) is complete.

       

      EDIT:

      I figured it out.  I just subtracted 1 from my CurrentDate in my CompletedDays variable

       

      WTD Yesterday =
      VAR CurrentDate = TODAY()
      VAR StartOfWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
      VAR EndOfWeek = StartOfWeek + 4
      VAR DaysInWeek = 5
      VAR CompletedDays = MIN(CurrentDate-1, EndOfWeek) - StartOfWeek + 1
      RETURN
          CompletedDays / DaysInWeek

       

      Thank you