Want to compare stocks based on current and previous date.

[Jeetshah008]

Hello Team! 

I have the stocks data coming in an excel daily and would like to compare if the same stock was present in previous day or not. Screenshot of the the data is available below.

My aim is to visualise 3 things:-

1.  list of stocks present in previous day and today.

2.  Stock List which indicates for how many consecutive days the stock is present.

2.  List of stocks present in previous day but removed today.

Note :- I would also like to get your help in eliminating weekends and public holidays so that the dashboard doesn't fail after a holiday.

Thanks in Advance!

Jeet

[Jeetshah008]

Thanks Xiaoxin Sheng, for the detailed response. I was trying to replicate the solution but it seems there was a error for the 1st question. Below is the snippet of the error.

Waiting for your response. Thanks in advance.

Thanks,
Jeet Shah

[v-shex-msft]

Hi @Jeetshah008 ,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @Jeetshah008,

Did this report file records fully replaced daily or just append new records after the previous? If you mean the first one, current power bi doesn't include historical data feature, you may need to add a bridge database to store these daily records. Then you can get data from the temp database and design report.

#1, You can add two variables to use current date and previous date as condition to extract stock list and use INTERSECT function to compare them and use CONCATENATEX function to merge them to text strings.

stock formula1=
VAR currDate =
    MAX ( table1[date] )
VAR prevDate =
    CALCULATE (
        MAX ( table1[date] ),
        FILTER ( ALLSELECTED ( table1 ), [date] < currDate )
    )
VAR currList =
    VALUE ( table1[Stock] )
VAR prevList =
    CALCULATETABLE (
        VALUE ( table1[Stock] ),
        FILTER ( ALLSELECTED ( table1 ), [date] = prevDate )
    )
RETURN
    CONCATENATEX ( INTERSECT ( currList, prevList ), [Stock], "," )

#2, You can extract the current date and stock as variable to find out the nearest not include current stock date and use them as condition to filter on the table to get the match day count.

stock formula2 =
VAR currStock =
    SELECTEDVALUE ( table1[Stock] )
VAR currDate =
    MAX ( table1[date] )
VAR prevRange =
    CALCULATE (
        MAX ( table1[date] ),
        FILTER (
            ALLSELECTED ( table1 ),
            [date] < currDate
                && NOT ( currStock IN VALUES ( table1[Stock] ) )
        )
    )
VAR nextRange =
    CALCULATE (
        MIN ( table1[date] ),
        FILTER (
            ALLSELECTED ( table1 ),
            [date] > currDate
                && NOT ( currStock IN VALUES ( table1[Stock] ) )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( table1[date] ) ),
        FILTER (
            ALLSELECTED ( table1 ),
            AND ( [date] > prevRange && [date] < nextRange, table1[Stock] = currStock )
        )
    )

#3, You can use EXCEPT function to replace INTERSECT to get the required stock list:

stock formula3 =
VAR currDate =
    MAX ( table1[date] )
VAR prevDate =
    CALCULATE (
        MAX ( table1[date] ),
        FILTER ( ALLSELECTED ( table1 ), [date] < currDate )
    )
VAR currList =
    VALUE ( table1[Stock] )
VAR prevList =
    CALCULATETABLE (
        VALUE ( table1[Stock] ),
        FILTER ( ALLSELECTED ( table1 ), [date] = prevDate )
    )
RETURN
    CONCATENATEX ( EXCEPT ( prevList, currList ), [Stock], "," )

INTERSECT function (DAX) - DAX | Microsoft Learn

CONCATENATEX function (DAX) - DAX | Microsoft Learn

EXCEPT function (DAX) - DAX | Microsoft Learn

For skip weekend and holidays, you may need to prepare a table with holiday date list, then you can exclude match date ranges before calculations. Weekend can be excluded by WEEKDAY function.

Regards,

Xiaoxin Sheng