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
Alealmiron_
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
v-yulgu-msft
Employee
Employee

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
v-yulgu-msft
Employee
Employee

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.
Alealmiron_
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.

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.