Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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
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.
could you please share the sample data?
What is the output for the first measure? 27?
Proud to be a Super User!
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |