Price changes monitoring using date range

[Anonymous]

I have some data that I would like to track the changes in prices across a specified time period. I'd like to use a date slider and display the changes in prices wither as a +/- and the difference in price change. 

My data is only avalilable in a single data source, but i dont have a clue of how to split this into particular time snapshots.

Here's a sample of my data

Stock	Date	Price
A	1/01/2021	20
B	2/01/2021	25
C	3/01/2021	22
D	4/01/2021	21
E	5/01/2021	19
F	6/01/2021	10
G	7/01/2021	40
H	8/01/2021	50
I	9/01/2021	25
A	10/02/2021	27
B	10/01/2021	28
C	11/01/2021	100
A	01/03/2021	40
B	12/01/2021	35
C	13/01/2021	79

I'd like to track stocks A's change in price when i select February month it shows me the previous month price is $20 and there has been a $17 change in price. How could I go about this especially with different years/months and days?

[v-cazheng-msft · Accepted solution]

Hi @Anonymous ,

You may try this solution.

1 Create a Calendar table with the min date and max date from the sample data

2 Create a Month column and Month Number column

3 Use Month column in the previous step to create a Slicer

4 Create a Measure and use it to conditional format Price column and Stage column

Changes =
VAR selectedMon =
    CALCULATE (
        MAX ( 'Calendar'[MonNum] ),
        'Calendar'[Month] = SELECTEDVALUE ( 'Calendar'[Month] )
    )
VAR Month_No =
    MONTH ( MAX ( 'Table'[Date ] ) )
VAR MaxPrevMon =
    CALCULATE (
        MAX ( 'Table'[Date ] ),
        ALLEXCEPT ( 'Table', 'Table'[Stock ] ),
        MONTH ( 'Table'[Date ] ) = selectedMon - 1
    )
VAR MaxNextMon =
    CALCULATE (
        MAX ( 'Table'[Date ] ),
        ALLEXCEPT ( 'Table', 'Table'[Stock ] ),
        MONTH ( 'Table'[Date ] ) = selectedMon
    )
VAR MaxPrevMon_No =
    MONTH ( MaxPrevMon )
VAR MaxNextMon_No =
    MONTH ( MaxNextMon )
VAR CurrOrPrevMon =
    SWITCH (
        TRUE (),
        ( Month_No = selectedMon )
            && NOT ( ISBLANK ( MaxPrevMon ) )
                && ( MaxNextMon_No = MaxPrevMon_No + 1 ), "lightgreen",
        ( Month_No = selectedMon - 1 )
            && NOT ( ISBLANK ( MaxNextMon_No) )
                && ( MaxNextMon_No = MaxPrevMon_No + 1 ), "Yellow",
        ""
    )
RETURN
    CurrOrPrevMon

Then, the result should look like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[VahidDM]

Hi @Anonymous 

Can you add more details about the desired output, or share a sample of the result here?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Here's a sample file/table 

The idea is to monitor any changes in prices and stage for a particular stock at a particular date (month). For example if I use a Calender Month slicer (February) i should see/highlight Stock A has progressed from "In discusssion" to "Assestment" and Price changed from $50 to $94. All changes highlighted should be relative to the previous month. When I select Month (March) A should have changed from "Assestment" to "Approved" and Price has dropped to $54 from $94 in Feb. 

Hope this helps

Stock	Date	Price	Stage
A	1/01/2021	50	In Discussion
B	1/01/2021	44	In Discussion
C	1/01/2021	19	In Discussion
D	1/01/2021	32	In Discussion
E	1/01/2021	64	In Discussion
F	1/01/2021	50	In Discussion
A	2/02/2021	94	Assestment
B	2/02/2021	66	Assestment
C	2/02/2021	50	Assestment
D	2/02/2021	25	Assestment
E	2/02/2021	100	Assestment
A	3/03/2021	54	Approved
B	3/03/2021	75	Approved
C	3/03/2021	21	Approved
D	3/03/2021	28	Approved
A	1/04/2021	61	Trading
B	1/04/2021	4	Trading
C	1/04/2021	76	Trading
D	1/04/2021	48	Trading
E	1/04/2021	74	Assestment
F	1/04/2021	0	Assestment
Y	2/02/2021	95	Trading
Z	24/02/2021	82	Approved
M	14/01/2021	48	In Discussion
P	9/03/2021	14	In Discussion
Q	6/02/2021	95	In Discussion