cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomBLG
Helper I
Helper I

Graph Last 12 Weeks based on Week Slicer selection

Hi everyone,

 

I currently have a report showing a Weekly table of data and upon clicking on each KPI entry, it populates a graph with Hourly values.

TomBLG_0-1649694760999.png

This is possible thanks to the following measure as the main table is an unpivotted, curated, version of the tables in the model:

KPI Selection = 
SWITCH(
    TRUE(),
    VALUES('KPI_Table'[KPI]) = "Metric 1", SUM('Hourly'[Metric1]),
    VALUES('KPI_Table'[KPI]) = "Metric 2", SUM('Hourly'[Metric2]),
    VALUES('KPI_Table'[KPI]) = "Metric 3", SUM('Hourly'[Metric3]),
    VALUES('KPI_Table'[KPI]) = "Metric 4", SUM('Hourly'[Metric4]),
    VALUES('KPI_Table'[KPI]) = "Metric 5", SUM('Hourly'[Metric5]),
    BLANK ()
)

 

To this report, I would like to add the following:

 

1 - A similar area graph of Hourly data for the last 12 weeks based off the YearWeek slicer (2016 data points, 168 * 12)

2 - Bar graph of Daily data for the last 12 weeks based off the YearWeek slicer (84 data points, 7 * 12)

3 - Bar graph of Weekly data for the last 12 weeks based off YearWeek slicer (12 data points)

 

Here's a Dummy pbix file with the data model replicated as best as possible.

 

I've had a thread open on this a long time ago, nearly solved it, and was left on my backlog ever since.

 

Here's a list of measures I've tried and believe might have gotten close to what I needed:

 

12Weeks =
VAR ReferenceDate = MAX ( 'Calendar'[Date] )
VAR StartDate = DATESINPERIOD ('Calendar'[Date], ReferenceDate, -84, DAY)

VAR Result =
CALCULATE(
[KPI Selection],
REMOVEFILTERS( 'Calendar' ),
KEEPFILTERS( StartDate )
)
RETURN
Result
12Weeks.2 = 
VAR ReferenceDate = CALCULATE( MAX ( 'Daily'[Daily] ), 'Daily'[YearWeek]=SELECTEDVALUE(ISO50001[YearWeek]))
VAR Result = 
CALCULATE([KPI Selection]),DATESINPERIOD('Daily'[Daily],LASTDATE('Daily'[Daily] ) ,-84,day)) 

RETURN
Result 
L12W  = CALCULATE([KPI Selection]),REMOVEFILTERS('Calendar'),
KEEPFILTERS(DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-84,day)), USERELATIONSHIP(Weekly[YearWeek],KPI_Table[YearWeek]))

 

Lastly, this is how the "Metric1" graphs should look like if YearWeek 2152 is selected:

TomBLG_1-1649696905503.png

 

Thank you in advance for taking some time to look into this,

Tom

2 REPLIES 2
amitchandak
Super User
Super User

@TomBLG , Based on what I got

if you select a date and want to display more than that, you need independent date table

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -84 
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

Also, check

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Hi @amitchandak,

 

Unfortunately I wasn't able to get to the solution I need with your measure nor with the links provided. Despite the new Date1 table (which I named Calendar1), it will still display just the week selected:

TomBLG_0-1649840001071.png

Another matter is the fact that this is only displaying properly on the area charts. For a bar chart, it will display all Metrics and instead highlight just the selected one, rather than simply showing the selected metric.

 

I suspect that rather than doing this:

new measure = 
var _max = maxx(allselected(Calendar1),Calendar1[Date])
var _min = _max -84 
return
calculate( [KPI Selection], filter('Calendar', 'Calendar'[Date] >=_min && 'Calendar'[Date] <=_max))

I need to somehow integrate this measure in that one instead of calling [KPI Selection] in the calculate:

KPI Selection = 
SWITCH(
    TRUE(),
    VALUES('KPI_Table'[KPI]) = "Metric 1", SUM('Hourly'[Metric1]),
    VALUES('KPI_Table'[KPI]) = "Metric 2", SUM('Hourly'[Metric2]),
    VALUES('KPI_Table'[KPI]) = "Metric 3", SUM('Hourly'[Metric3]),
    VALUES('KPI_Table'[KPI]) = "Metric 4", SUM('Hourly'[Metric4]),
    VALUES('KPI_Table'[KPI]) = "Metric 5", SUM('Hourly'[Metric5]),
    BLANK ()
)

 If you see where the problem is and could change and send the pbix file back I would appreciate it immensely:

Power BI File

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors