cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

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

2 ACCEPTED SOLUTIONS
Super User

@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

Helper III

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 )``````

2 REPLIES 2
Super User

@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

Helper III

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 )``````

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors