Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Justinnn
Regular Visitor

Index 100 based on sliced year and Item

Hi all,

I'm new to PowerBI, so help would be greatly appreciated. Especially since the function 'Index' has come out, the problem should be easier for the experts to solve.

 

Data

I have the following Table called 'Spend':

 

DateItemPrice
01/03/2022Gas60
01/04/2022Gas63
01/05/2022Gas64
01/03/2022Timber20
01/04/2022Timber22
01/05/2022Timber26
01/03/2022Plastic4
01/04/2022Plastic5
01/05/2022Plastic6

I want to have a dynamic index model based on the slicers for "date". The index should be 100 (Price / Base Price) with a flexible baseline. For Gas, the index numbers would be 100, 105 and 106,7. However, if the date is changed to April 2022, the index values should become 0, 100, 101.6. Furthermore, it should start again for each unique Item (i.e., Plastic & Timber).

 

Thank you for helping out!

Additional Screenshot:

Justinnn_0-1675849721108.png

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Justinnn 
Please refer to attached sample file with the solution

1.png

Price Index = 
VAR BaseTable = 
    CALCULATETABLE ( 
        TOPN ( 1, 'Table', 'Table'[Date], ASC ),
        ALLEXCEPT ( 'Table', 'Table'[Item] ), 
        ALLSELECTED ( 'Table'[Date] ) 
    )
VAR BasePrice = 
    MAXX ( BaseTable, 'Table'[Price] )
VAR CurrentPrice = 
    SELECTEDVALUE ( 'Table'[Price] )
VAR REsult =
    DIVIDE ( CurrentPrice, BasePrice ) * 100
RETURN 
    IF (
        NOT ISEMPTY ( CALCULATETABLE ( 'Table', ALL ( 'Table'[Date] ) ) ),
        DIVIDE ( CurrentPrice, BasePrice ) * 100 + 0
    )

View solution in original post

2 REPLIES 2
Justinnn
Regular Visitor

Hi @tamerj1 ,

 

Thank you so much for helping me! That was 99% of the code. However, I wanted that the index itself also became flexible. This was my final code for others for future reference.

Price Index = 
VAR SelectedDate = MINX(ALLSELECTED('Table'[Date]), 'Table'[Date])
VAR BaseTable = 
    CALCULATETABLE ( 
        TOPN ( 1, 'Table', 'Table'[Date], ASC ),
        ALLEXCEPT ( 'Table', 'Table'[Item] ), 
        'Table'[Date] = SelectedDate 
    )
VAR BasePrice = 
    MAXX ( BaseTable, 'Table'[Price] )
VAR CurrentPrice = 
    SELECTEDVALUE ( 'Table'[Price] )
VAR REsult =
    DIVIDE ( CurrentPrice, BasePrice ) * 100
RETURN 
    IF (
        NOT ISEMPTY ( CALCULATETABLE ( 'Table', ALL ( 'Table'[Date] ) ) ),
        DIVIDE ( CurrentPrice, BasePrice ) * 100 + 0
    ) 

 

tamerj1
Super User
Super User

Hi @Justinnn 
Please refer to attached sample file with the solution

1.png

Price Index = 
VAR BaseTable = 
    CALCULATETABLE ( 
        TOPN ( 1, 'Table', 'Table'[Date], ASC ),
        ALLEXCEPT ( 'Table', 'Table'[Item] ), 
        ALLSELECTED ( 'Table'[Date] ) 
    )
VAR BasePrice = 
    MAXX ( BaseTable, 'Table'[Price] )
VAR CurrentPrice = 
    SELECTEDVALUE ( 'Table'[Price] )
VAR REsult =
    DIVIDE ( CurrentPrice, BasePrice ) * 100
RETURN 
    IF (
        NOT ISEMPTY ( CALCULATETABLE ( 'Table', ALL ( 'Table'[Date] ) ) ),
        DIVIDE ( CurrentPrice, BasePrice ) * 100 + 0
    )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors