Limit Range of Sparkline in Table Visual

[ksoukup]

I have the table below that calculates trailing 12 month sales, margin, sales price/lb, cost/lb, etc. The DAX for these measures is basically the same and I have put an example below. All of the fields calculate correctly. The issue arises when I add a sparkline to the table-- the sparkline is displaying ALL months, not just the last 12 months, and this is bogging down the visual. Is there any way that I can limit what the sparkline is displaying? I have tried adding a visual level filter and using a relative date, for dates in the last 12 months; this gives me the desired look, but doesn't update when the date slicer is adjusted. I think what I need is a disconnected date table, but I haven't been able to do that successfully.

Sales Trailing 12 Mos =

CALCULATE(

    [Sales],

    DATESINPERIOD(

        'Date Table'[Date], ENDOFMONTH('Date Table'[Date]), -12, MONTH

    )

)

[Anonymous]

Any luck with this? Having the same issue.

[v-luwang-msft]

Hi @ksoukup ，

Could you pls share your pbix file ,remember to remove confidential data.

Best Regards

Lucien

[ksoukup]

Hi @v-luwang-msft,

Here are links to the .pbix and the .xlsx with dummy data.

pbix file
xlsx data file

[ksoukup]

Getting closer... in the file I created a measure called "Date within 1 Yr from Max", which will give a 1 or 0 value for dates in the last 365 days based on the max slicer selection, see measure and first image.

However, when I add this measure as a visual-level filter (where measure = 1), it clears my table (see second image)   😞

I do not know now if a disconnected date table is the best route, as the main date slicer drives multiple other visuals.

Date within 1 Yr from Max =

VAR _maxdateselection =

    CALCULATE(

        MAX('Date Table'[Date]),

        ALLSELECTED()

    )

VAR _date = MIN('Date Table'[Date])

RETURN

IF(_date >= _maxdateselection - 365, 1, 0)