Different measure per drill down level

[Anonymous]

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?

[parry2k]

@Anonymous Can you explain the problem with sample data? 

[Anonymous]

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

[amitchandak]

@Anonymous , Can you redescribe your problem with data. 

For the  week you should use week rank to go in past refer my example

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Or

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

[Anonymous]

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).

[v-lid-msft · Accepted 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]

Bingo!  This is exactly what I needed.  Thank you!

[amitchandak]

@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)

[Anonymous]

Hi Amit,

Yes, that is technically what my Dates.Weeks is.  The "Week Begin Date" starting on Mondays.