cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Days or Dates compared

Hi, 

 

I am presented with the following case, I have to make a report by compared prices of different price lists, for example in the list 1 a product to cost 500 and in the list 2 costs 800, if I want to perform the two compared to know if there was variation of prices as Should I do it? At this time I have a table where I have all my products with their prices and price date, to compare different dates as I do? Or should I have a table for current price and a table for previous price?

 

I hope you can help me

Best regards,

 

Alejandro

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Hi @Alealmiron_ ,

 

You need an extra table to display all unique list items which will be added into slicer later.  Make sure this table 'List item' is unrelated to source data table 'Sheet15'

List item = VALUES(Sheet15[List])

1.PNG

 

Create measures.

Actual Price =
CALCULATE (
    SUM ( Sheet15[Price] ),
    FILTER (
        ALLSELECTED ( Sheet15 ),
        Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
            && Sheet15[List] = SELECTEDVALUE ( 'List item'[List] )
    )
)

Previous Price =
VAR currentdatedate =
    CALCULATE (
        MAX ( Sheet15[Date] ),
        FILTER (
            ALLSELECTED ( Sheet15 ),
            Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
                && Sheet15[List] = SELECTEDVALUE ( 'List item'[List] )
        )
    )
VAR previousdate =
    CALCULATE (
        MAX ( Sheet15[Date] ),
        FILTER (
            ALLSELECTED ( Sheet15 ),
            Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
                && Sheet15[Date] < currentdatedate
        )
    )
RETURN
    CALCULATE (
        SUM ( Sheet15[Price] ),
        FILTER (
            ALLSELECTED ( Sheet15 ),
            Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
                && Sheet15[Date] = previousdate
        )
    )

variaction = ([Previous Price]-[Actual Price])/[Actual Price]

ESTADO = IF([variaction]<0,"BAIA","SUBA")

2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Regular Visitor

 

 

 

ejemplo.PNG

 

I enclose an example of how I want to visualize in the dashboard, I must have a filter above where the user can filter the list you want to use, but when filtering I need both Actual Price and Previous Price to show the data according to the filtered list, if you filter in this case List 2, Current price must be that of list 2 and previous price that of list 1. and below I detail how I have built my data in the table to help me with any suggestion of formula or how I should see my data to make it easier.

Highlighted
Microsoft
Microsoft

Hi @Alealmiron_ ,

 

You need an extra table to display all unique list items which will be added into slicer later.  Make sure this table 'List item' is unrelated to source data table 'Sheet15'

List item = VALUES(Sheet15[List])

1.PNG

 

Create measures.

Actual Price =
CALCULATE (
    SUM ( Sheet15[Price] ),
    FILTER (
        ALLSELECTED ( Sheet15 ),
        Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
            && Sheet15[List] = SELECTEDVALUE ( 'List item'[List] )
    )
)

Previous Price =
VAR currentdatedate =
    CALCULATE (
        MAX ( Sheet15[Date] ),
        FILTER (
            ALLSELECTED ( Sheet15 ),
            Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
                && Sheet15[List] = SELECTEDVALUE ( 'List item'[List] )
        )
    )
VAR previousdate =
    CALCULATE (
        MAX ( Sheet15[Date] ),
        FILTER (
            ALLSELECTED ( Sheet15 ),
            Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
                && Sheet15[Date] < currentdatedate
        )
    )
RETURN
    CALCULATE (
        SUM ( Sheet15[Price] ),
        FILTER (
            ALLSELECTED ( Sheet15 ),
            Sheet15[Product] = SELECTEDVALUE ( Sheet15[Product] )
                && Sheet15[Date] = previousdate
        )
    )

variaction = ([Previous Price]-[Actual Price])/[Actual Price]

ESTADO = IF([variaction]<0,"BAIA","SUBA")

2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors