Show previous "months", but based on the index column instead of date

[m4xon]

Hi,

I have a weird case to solve. I would like to replace the logic of following measure (from SQLBI):

VAR NumOfMonths = -6
VAR ReferenceDate = MAX ( 'Date'[Date] )
VAR PreviousDates =
    DATESINPERIOD (
        'Previous Date'[Date],
        ReferenceDate,
        NumOfMonths,
        MONTH
    )
VAR Result =
    CALCULATE (
        SELECTEDMEASURE (),
        REMOVEFILTERS ( 'Date' ),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP ( 'Previous Date'[Date], 'Date'[Date] )
    )
RETURN Result

but instead of using Date column I need to use Index one.

I have two not related tables called _txtCalendarTbl & _txtCalendar_Q_tbl:

Txt	Index
2022-1	1
2022-2	2
2022-3	3
2022-4	4
2023-1	5
2023-2	6
2023-3	7
2023-4	8
2024-1	9
2024-2	10
2024-3	11
2024-4	12
2025-1	13
2025-2	14
2025-3	15
2025-4	16
2026-1	17

as a source of a slicer I'm using _txtCalendarTbl and when I select for example 2024-4 I would like to be to see something like that:

_txtCalendarTbl	Measure
2022-1	0
2022-2	0
2022-3	0
2022-4	0
2023-1	0
2023-2	0
2023-3	0
2023-4	0
2024-1	0
2024-2	0
2024-3	1
2024-4	1
2025-1	1
2025-2	0
2025-3	0
2025-4	0
2026-1	0

Thank you in advance!
Max

[amitchandak · Accepted solution]

@m4xon , Make sure you have a separate table with period and index. Joined back to your data table.

You can use measures like

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Index]=max(Period[Index])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Index]=max(Period[Index])-1))

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

[amitchandak · Accepted solution]

@m4xon , Make sure you have a separate table with period and index. Joined back to your data table.

You can use measures like

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Index]=max(Period[Index])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Index]=max(Period[Index])-1))

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

[m4xon · Accepted solution]

Hi, thank you for your reply, it has led me to a solution:

Selected Period =
VAR _selectedPeriod =
    CALCULATE (
        MIN ( _txtCalendarTbl[Index] ),
        FILTER ( ALL ( _txtCalendar_Q_tbl), _txtCalendar_Q_tbl[Index] >= MAX ( _txtCalendar_Q_tbl[Index] ) - 1 ),
        FILTER ( ALL ( _txtCalendar_Q_tbl), _txtCalendar_Q_tbl[Index] <= MAX ( _txtCalendar_Q_tbl[Index] ) + 1 ),
        USERELATIONSHIP ( _txtCalendarTbl[Index], _txtCalendar_Q_tbl[Index] )
    )
RETURN
    IF ( ISBLANK ( _selectedPeriod ), BLANK() , 1 )