Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kaye123
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
v-yiruan-msft
Community Support
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.

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.

kaye123_0-1671032222456.png

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. 

kaye123_2-1671032348427.png

 

kaye123_3-1671032469487.png

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!

 

 

 

amitchandak
Super User
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

 

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors