cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eliasayy
Impactful Individual
Impactful Individual

Index by group in table visual

hello everyone i have this table below i made below:

eliasayy_1-1673654763592.png

 

 

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
ASep-20101
AOct-2082
ANov-2003
ADec-2054
AJan-2105
BJun-2001
BJul-2002
BAug-2043
BSep-2094
BOct-2065
BNov-2006
BDec-2007
BJan-2108

 

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

eliasayy_2-1673654940714.png

 

desired result:

Product              Year-Month            Sales Measure           Index
ASep-20101
AOct-2082
BJun-2001
BJul-2002
BAug-2043
BSep-2094
BOct-2065


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

is this possibe?
@tamerj1 @FreemanZ 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1673668702306.png

 

 

Jihwan_Kim_0-1673668664398.png

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1673668702306.png

 

 

Jihwan_Kim_0-1673668664398.png

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors