cancel
Showing results for
Did you mean:
Frequent Visitor

## sparkline x-axis Last 10Weeks depending on slicer selection

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)
4 REPLIES 4
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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!

Super User

@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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Frequent Visitor

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!

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.