topic Re: sparkline x-axis Last 10Weeks depending on slicer selection in DAX Commands and Tips

sparkline x-axis Last 10Weeks depending on slicer selection

kaye123 — Mon, 05 Dec 2022 13:17:02 GMT

I am trying to create a sparkline that will only always show last 10 weeks or last 4 quarters depending on what is my Year selection from a slicer. As per checking in the net, I have to create a flexible dynamic table that will be the place holder of selected weeks.

I did a formula below but doesn't seem to work as a list of the new column/table. Appreacte any help. Thanks!

Quarter format sample = 2022_1

Week format sample = 2020_01

CODE:

Top4Q =

Var MaxQtr = Maxx(ALLSELECTED(FiscalCalendar),FiscalCalendar[FiscalQuarter])

VAR MinQtr = MaxQtr - 4

Return

CALCULATE(SUM(FiscalCalendar[FiscalQuarter]),FiscalCalendar[FiscalQuarter]>=MinQtr && FiscalCalendar[FiscalQuarter]<=MaxQtr)

Re: sparkline x-axis Last 10Weeks depending on slicer selection

amitchandak — Mon, 05 Dec 2022 15:17:25 GMT

kaye123 , for 10 week, you have to create separate week/date table with week rank

new column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measure

Last 10 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-10 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Re: sparkline x-axis Last 10Weeks depending on slicer selection

kaye123 — Tue, 06 Dec 2022 05:34:40 GMT

My week date is different. My week 1 is 1st week of november. and is on a character setup "2020_01". How can I use this code with this settings? Thanks!

Re: sparkline x-axis Last 10Weeks depending on slicer selection

Anonymous — Fri, 09 Dec 2022 09:46:52 GMT

Hi kaye123 ,

You can create a measure as below to get the values for last 4 quarters:

Top4Q = VAR _selqtr = SELECTEDVALUE ( FiscalCalendar[FiscalQuarter] ) VAR _year = LEFT ( _selqtr, 4 ) VAR _quarter = RIGHT ( _selqtr, 1 ) VAR _preyear = _year - 1 VAR _preqtr = _quarter - 1 VAR _maxqtr = IF ( VALUE ( _quarter ) = 1, VALUE ( _preyear & "0" & 4 ), VALUE ( _year & "0" & _quarter - 1 ) ) VAR _minqtr = IF ( VALUE ( _quarter ) = 1, VALUE ( _year - 1 & "0" & 1 ), VALUE ( _preyear & "0" & _quarter ) ) RETURN CALCULATE ( SUM ( FactTable[Value] ), FILTER ( FiscalCalendar, value(replace(FiscalCalendar[FiscalQuarter],5,1,"0")) >= _minqtr && value(replace(FiscalCalendar[FiscalQuarter],5,1,"0")) <= _maxqtr ) )

For the values on last x weeks, you can use the similar logis with the above ones....

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: sparkline x-axis Last 10Weeks depending on slicer selection

kaye123 — Wed, 14 Dec 2022 15:43:31 GMT

I tried this code. You're code is working if I am getting the computations. However, I don't want to use any value "FactTable[Value]" and compute it. The measure dax code should only list the top4 quarters (no otehr columns). For example, I selected 2022_04, the list should consist of 2022_04, 2022_03, 2022_01, 2022_00.

This new list in turn will be used in the x-axis of the sparkline. This should show only 4 quarters, instead of all the quarters I have in my historical data.

I tried revising the code to only list the value, however I am getting this error. This is the only thing I changed in your code.

Goal is, shorten this x-axis sparkline to only top4 for quarters. Y-axis will be the "FactTable[Value]" in which measure is already available.

Can you help me on this please? Thanks!