Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a hierarchy with 2 levels Dates.Weeks and Dates.Days. I have 2 measures [Weekly Average] and [Daily Average]. I need a new measure to switch between [Weekly Average] and [Daily Average] based on the selected hierarchy level.
My problem occurs on Mondays when there is only one value for Dates.Weeks and Dates.Days.
I've searched and found the below suggestions, but this does not seem to work for my situation.
https://community.powerbi.com/t5/Desktop/Different-measure-per-drill-down-level/m-p/627225
Suggestions?
Solved! Go to Solution.
Hi @Anonymous ,
We can try to create a measure as value field of chart to meet your requirement:
Measure =
IF (
ISINSCOPE ( Dates[Days] ),
CALCULATE ( [Daily Average] ),
CALCULATE ( [Weekly Average] )
)
We use ISINSCOPE function to verify current level and calculate different value
Best regards,
@Anonymous Can you explain the problem with sample data?
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.
Weekly Average = CALCULATE(SUM(Sales.Sales),
DATESBETWEEN(Dates.Days,
LASTDATE(Dates.Days)-34,
LASTDATE(Dates.Days)
)
) / 5
Daily Average = CALCULATE(SUM(Sales.Sales),
DATESBETWEEN(Dates.Days,
LASTDATE(Dates.Days)-34,
LASTDATE(Dates.Days)
)
) / 35
Weeks | Days | Sales |
3/23/2020 | 3/23/2020 | 110 |
3/23/2020 | 3/24/2020 | 111 |
3/23/2020 | 3/25/2020 | 112 |
3/23/2020 | 3/26/2020 | 113 |
3/23/2020 | 3/27/2020 | 114 |
3/23/2020 | 3/28/2020 | 115 |
3/23/2020 | 3/29/2020 | 116 |
3/30/2020 | 3/30/2020 | 117 |
3/30/2020 | 3/31/2020 | 118 |
3/30/2020 | 4/1/2020 | 119 |
3/30/2020 | 4/2/2020 | 120 |
3/30/2020 | 4/3/2020 | 121 |
3/30/2020 | 4/4/2020 | 122 |
3/30/2020 | 4/5/2020 | 123 |
4/6/2020 | 4/6/2020 | 124 |
4/6/2020 | 4/7/2020 | 125 |
4/6/2020 | 4/8/2020 | 126 |
4/6/2020 | 4/9/2020 | 127 |
4/6/2020 | 4/10/2020 | 128 |
4/6/2020 | 4/11/2020 | 129 |
4/6/2020 | 4/12/2020 | 130 |
4/13/2020 | 4/13/2020 | 129 |
4/13/2020 | 4/14/2020 | 128 |
4/13/2020 | 4/15/2020 | 127 |
4/13/2020 | 4/16/2020 | 126 |
4/13/2020 | 4/17/2020 | 125 |
4/13/2020 | 4/18/2020 | 124 |
4/13/2020 | 4/19/2020 | 123 |
4/20/2020 | 4/20/2020 | 122 |
4/20/2020 | 4/21/2020 | 121 |
4/20/2020 | 4/22/2020 | 120 |
4/20/2020 | 4/23/2020 | 119 |
4/20/2020 | 4/24/2020 | 118 |
4/20/2020 | 4/25/2020 | 117 |
4/20/2020 | 4/26/2020 | 118 |
4/27/2020 | 4/27/2020 | 119 |
4/27/2020 | 4/28/2020 | 120 |
4/27/2020 | 4/29/2020 | 121 |
4/27/2020 | 4/30/2020 | 122 |
4/27/2020 | 5/1/2020 | 123 |
4/27/2020 | 5/2/2020 | 124 |
4/27/2020 | 5/3/2020 | 125 |
@Anonymous , Can you redescribe your problem with data.
For the week you should use week rank to go in past refer my example
Or
Hi Amit,
Data added in previous post. So to clarify, I have a line and column chart with both Week Begin Date and Date along the X axis. If the visual is on Week Begin Date, I need the measure to show the Weekly Average, but if the user drills to daily level, I need the measure to show the daily average (for previous 5 weeks or previous 35 days).
Hi @Anonymous ,
We can try to create a measure as value field of chart to meet your requirement:
Measure =
IF (
ISINSCOPE ( Dates[Days] ),
CALCULATE ( [Daily Average] ),
CALCULATE ( [Weekly Average] )
)
We use ISINSCOPE function to verify current level and calculate different value
Best regards,
Bingo! This is exactly what I needed. Thank you!
@Anonymous , Not sure I got it. But you can create Monday to Sunday week like
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Hi Amit,
Yes, that is technically what my Dates.Weeks is. The "Week Begin Date" starting on Mondays.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |