Forum Discussion

linusb's avatar
linusb
Frequent Visitor
6 years ago
Solved

Help with measure

Hello,

I'm new to PowerBI and I'm trying to get a measure working that looks at what WeekNum is selected, and from that calculates the available capacity.

 

I've created 2 measures.

// To select to current week

Selected Week = CALCULATE(MIN(Dates[Weeknum]),
FILTER(Dates,
Dates[IsDateHistory] = 0)
)
// To calculate remaining Manufacturing time
Available Capacity Inc Past =
CALCULATE([Remaining Manuf Time],
FILTER(ALL(Dates),
Dates[Weeknum] <= [Selected Week]
)
 
No matter what weeknum I select in my graph the wrong amount is calculated, but the meaure Selected Week calculates correctly. If I change the measure "Selected week" to a hardcoded number (i.e 27), then the correct value is calculated.
Can someone help me understand what isn't working.
 
Thanks
Linus
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi linusb ,

     

    Maybe you can do like this.

    Create  a calculated column like this and use hierarchical slicer.

     

    Mark the current weeknum = 
VAR x = WEEKNUM( TODAY(), 2 )
RETURN
IF(
    x = [Weeknum],
    "Current weeknum",
    "Other weeknum"
)

     

    After you set the default option of the slicer, as the data is updated, the default option of the slicer is always the current week number.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    linusb without looking how are the relationship etc , try following

     

    // To select to current week
Selected Week = CALCULATE(MIN(Dates[Weeknum]),
Dates[IsDateHistory] = 0
)

// To calculate remaining Manufacturing time
Available Capacity Inc Past =
CALCULATE([Remaining Manuf Time],
FILTER(ALL(Dates),
Dates[Weeknum] <= [Selected Week]
)
)

or

// To calculate remaining Manufacturing time
Available Capacity Inc Past =
VAR __selectedWeek =  CALCULATE(MIN(Dates[Weeknum]),
Dates[IsDateHistory] = 0
)
RETURN
CALCULATE([Remaining Manuf Time],
FILTER(ALL(Dates),
Dates[Weeknum] <= __selectedWeek
)
)

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    linusb 

     

    could you please share the sample data?

     

    What is the output for the first measure? 27?

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi linusb ,

     

    You don't need to create the [Selected Week ] measure, just need to like this.

    Available Capacity Inc Past = 
CALCULATE(
    [Remaining Manuf Time],
    FILTER(
        ALL(Dates),
        Dates[Weeknum] <= MAX(Dates[Weeknum])
    )
)

    Hope this is what you want, if not please give example data and carefully describe the results you want.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • linusb's avatar
      linusb
      Frequent Visitor
      6 years ago

      Thanks for the replys.

       

      This solution almost does the trick. The problem is, that I want the "Selected Week" to be the current week, when no weeknum is selected. 

       

      If I have a variable that I want to use in serveral measures, is it better to create the variable as it's own measure or define it as "local" variable in each measure?

       

      I've also tried using:

      Selected Week = SELECTEDVALUE(Dates[Weeknum])

       

      Available Capacity Include Past = 
 CALCULATE([Remaining Manuf Time],
            FILTER(ALL(Dates),
                            Dates[Weeknum] <= [Selected Week])
                                )
            )

      This gives my the wrong result (always 16676,75 not matter what week I select) 16677 equals the total amount of remaining manufacturing time, calculated by the measure [Remaining Manuf Time].

       

      If I merge [Selected Week] into the measure:

      Available Capacity Include Past = 
 CALCULATE([Remaining Manuf Time],
            FILTER(ALL(Dates),
                            Dates[Weeknum] <= SELECTEDVALUE(Dates[Weeknum])
                                )
            )

      Then the measure will calculate the correct values.

       

      I don't understand, why this is happening.

       

      Best regards,

      // Linus

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi linusb ,

         

        Maybe you can do like this.

        Create  a calculated column like this and use hierarchical slicer.

         

        Mark the current weeknum = 
VAR x = WEEKNUM( TODAY(), 2 )
RETURN
IF(
    x = [Weeknum],
    "Current weeknum",
    "Other weeknum"
)

         

        After you set the default option of the slicer, as the data is updated, the default option of the slicer is always the current week number.

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.