Forum Discussion

ValentinBIA's avatar
ValentinBIA
Resolver I
8 years ago
Solved

Variance over week (DAX)

Good morning Power BI community !

 

I'm sure my problem has an easy solution, but I haven't had the time to read "Learn to write DAX", I'm still focused on "M is for (data) monkeys" (almost done though).

 

I'm trying to get the variance over week of my rentals but I cannot figure out how to do it, since PowerBI does not have a function such as YTD for weeks.

 

To get the variance, I know I need to first have the value of the previous week, and this is where I struggle.

 

Image and video hosting by TinyPic

 

 Here is an image of my dataset. I already have a measure on my report for the total value of each week (Total Rentals = SUM('Journeys by station'[Value])). How can I get the same result for the previous week ?

 

Thank you very much for your help.

 

Best,

 

Valentin

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    This should work:

     

    Total Rentals Previous Week =
VAR selectedWeek = SELECTEDVALUE('Journeys by station'[Week])
RETURN
    CALCULATE(
        [Total Rentals],
        'Journeys by station'[Week] = selectedWeek - 1
    )

    You may need some adjustment if you have values for the same week in different years.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    This should work:

     

    Total Rentals Previous Week =
VAR selectedWeek = SELECTEDVALUE('Journeys by station'[Week])
RETURN
    CALCULATE(
        [Total Rentals],
        'Journeys by station'[Week] = selectedWeek - 1
    )

    You may need some adjustment if you have values for the same week in different years.

    • ValentinBIA's avatar
      ValentinBIA
      Resolver I
      8 years ago

      Hey Anonymous, thank you very much, that is exactly what I was looking for !