Index by group in table visual

[Anonymous]

hello everyone i have this table below i made below:

the behavior of the table is if i select a date on the slicer, it will show from start date till selected date

what i want is to add an index to the table according to the slicer and by product

so desired result is :

Product	Year-Month	Sales Measure	Index
A	Sep-20	10	1
A	Oct-20	8	2
A	Nov-20	0	3
A	Dec-20	5	4
A	Jan-21	0	5
B	Jun-20	0	1
B	Jul-20	0	2
B	Aug-20	4	3
B	Sep-20	9	4
B	Oct-20	6	5
B	Nov-20	0	6
B	Dec-20	0	7
B	Jan-21	0	8

but it should change when i seelct another date from slicer anotehr example :

desired result:

Product	Year-Month	Sales Measure	Index
A	Sep-20	10	1
A	Oct-20	8	2
B	Jun-20	0	1
B	Jul-20	0	2
B	Aug-20	4	3
B	Sep-20	9	4
B	Oct-20	6	5

Note that index should be by date(Year-Month) sort as in the example shown

is this possibe?
@tamerj1 @FreemanZ 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales on or before slicer: = 
VAR _slicerdate =
    MAX ( 'Slicer table'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        KEEPFILTERS ( 'Calendar'[Date] <= _slicerdate )
    )

Index expected result: =
VAR _startdate =
    CALCULATE ( MIN ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR _monthyearbyproducttable =
    CALCULATETABLE (
        SUMMARIZECOLUMNS ( 'Calendar'[Month-Year], 'Calendar'[Month-Year sort] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] >= _startdate )
    )
RETURN
    IF (
        NOT ISBLANK ( [Sales on or before slicer:] )
            && HASONEVALUE ( 'Calendar'[Month-Year] ),
        COUNTROWS (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                _monthyearbyproducttable,
                ORDERBY ( 'Calendar'[Month-Year sort], ASC )
            )
        )
    )