Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure Calculate until last week

I'm trying to get a measure right. 

 

Certain grades are given and I want to see how they progress every week. For that I try to write a measure that has the average of the grade number and filters until the last week.

 

Unfortunately I cannot get a dynamic week date where the measure has the current week minus 1 (so that it shows the average until last week). Hope someone can help out. 

 

test =

calculate(AVERAGE(table[number]),WEEKNUM('Calendar'[Date])-1)
Need Help
  • jppv20's avatar
    jppv20
    4 years ago

    Hi Anonymous ,

     

    First, create a weeknumber column in your calendar table:

    WeekNumber = WEEKNUM('Calendar'[Date],2)
     
    Then create a measure like this:
    test =
    var Maxdate = WEEKNUM(TODAY(),2)-1
    return
    CALCULATE(AVERAGE('Table'[Number]),'Calendar'[WeekNumber]<=Maxdate)
     
    If I answered your question, please mark it as a solution to help other members find it more quickly.

1 Reply

  • jppv20's avatar
    jppv20
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous ,

     

    First, create a weeknumber column in your calendar table:

    WeekNumber = WEEKNUM('Calendar'[Date],2)
     
    Then create a measure like this:
    test =
    var Maxdate = WEEKNUM(TODAY(),2)-1
    return
    CALCULATE(AVERAGE('Table'[Number]),'Calendar'[WeekNumber]<=Maxdate)
     
    If I answered your question, please mark it as a solution to help other members find it more quickly.