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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ase
Frequent Visitor

Making an index for prices

Good afternoon,

 

I am fairly new to PowerBI and I have a slight issue with creating a dax measure, which takes the price of a product in the beginning of the period as 100 (Base) and then based on the movements of the price over time gives me the percentage increase/decrease in price. 

 

Below is a simplification of the data to ilustrate the issue. 

 

ase_0-1654386857311.png

 

Could someone please help me out with the creation of the measure? 

 

Thank you very much in advance!

Kind regards,

B

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @ase 
Please refer to thr sample file with the solution https://www.dropbox.com/t/YtiyMYOrhy8EdsCp

Your method of calculating the percentage does not seem to be consistant. Please advise if I am missing somthing

Increse/Decrese = 
VAR ProductTable = CALCULATETABLE ( Prices, ALLEXCEPT ( Prices, Prices[Product] ) )
VAR BaseDate = MINX ( ProductTable, Prices[Date] )
VAR CurrentDate = MAX ( Prices[Date] )
VAR BasePrice = MAXX ( FILTER ( ProductTable, Prices[Date] = BaseDate ), Prices[Price] )
VAR CurrentPrice = MAX ( Prices[Price] )
VAR DiffirencePercent = FORMAT ( DIVIDE ( ABS ( CurrentPrice - BasePrice ), BasePrice ), "Percent" )
RETURN
    IF ( 
        CurrentDate = BaseDate,
        "Base Price",
        IF ( 
            CurrentPrice >= BasePrice,
            DiffirencePercent & " Increase",
            DiffirencePercent & " Decrease"
        )
    )

1.png

View solution in original post

2 REPLIES 2
ase
Frequent Visitor

Thank you very much!

tamerj1
Super User
Super User

Hi @ase 
Please refer to thr sample file with the solution https://www.dropbox.com/t/YtiyMYOrhy8EdsCp

Your method of calculating the percentage does not seem to be consistant. Please advise if I am missing somthing

Increse/Decrese = 
VAR ProductTable = CALCULATETABLE ( Prices, ALLEXCEPT ( Prices, Prices[Product] ) )
VAR BaseDate = MINX ( ProductTable, Prices[Date] )
VAR CurrentDate = MAX ( Prices[Date] )
VAR BasePrice = MAXX ( FILTER ( ProductTable, Prices[Date] = BaseDate ), Prices[Price] )
VAR CurrentPrice = MAX ( Prices[Price] )
VAR DiffirencePercent = FORMAT ( DIVIDE ( ABS ( CurrentPrice - BasePrice ), BasePrice ), "Percent" )
RETURN
    IF ( 
        CurrentDate = BaseDate,
        "Base Price",
        IF ( 
            CurrentPrice >= BasePrice,
            DiffirencePercent & " Increase",
            DiffirencePercent & " Decrease"
        )
    )

1.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors