sparkline x-axis Last 10Weeks depending on slicer selection

[kaye123]

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)

[v-yiruan-msft]

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

[kaye123]

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!

[amitchandak]

@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

[kaye123]

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!