Search code examples
powerbidaxpowerbi-desktopmeasure

DAX A Day-on-Day change to compare multiple Stocks in a Table

Here is the Sample PBIX File - Here

Sample Data -

StockQuoteTable StartDate EndDate QuotePull.Column1.CH_SYMBOL QuotePull.Column1.CH_TRADE_HIGH_PRICE QuotePull.Column1.CH_TRADE_LOW_PRICE QuotePull.Column1.CH_OPENING_PRICE QuotePull.Column1.CH_CLOSING_PRICE QuotePull.Column1.CH_LAST_TRADED_PRICE QuotePull.Column1.CH_PREVIOUS_CLS_PRICE QuotePull.Column1.CH_TOT_TRADED_QTY QuotePull.Column1.CH_TOT_TRADED_VAL QuotePull.Column1.CH_52WEEK_HIGH_PRICE QuotePull.Column1.CH_52WEEK_LOW_PRICE QuotePull.Column1.CH_TOTAL_TRADES QuotePull.Column1.CH_TIMESTAMP QuotePull.Column1.VWAP
HDFCBANK 01-Jan-23 31-Dec-23 HDFCBANK 1714.9 1696 1697 1709.25 1708.65 1705.25 12505713 21353650105 1757.5 1460.25 264143 29-Dec-23 1707.51
HDFCBANK 01-Jan-23 31-Dec-23 HDFCBANK 1721.4 1702 1709.3 1705.25 1708.85 1703.3 22038235 37713047859 1757.5 1460.25 423062 28-Dec-23 1711.26
HDFCBANK 01-Jan-23 31-Dec-23 HDFCBANK 1706.5 1678.6 1681.5 1703.3 1702.1 1682.45 13504539 22901880537 1757.5 1460.25 321373 27-Dec-23 1695.87
HDFCBANK 01-Jan-23 31-Dec-23 HDFCBANK 1685.95 1668.55 1673.25 1682.45 1684.1 1670.85 9022928 15144582177 1757.5 1460.25 243596 26-Dec-23 1678.46
HDFCBANK 01-Jan-23 31-Dec-23 HDFCBANK 1685.9 1667.1 1683.6 1670.85 1668.7 1686.7 24289425 40702711528 1757.5 1460.25 450284 22-Dec-23 1675.74
HDFCBANK 01-Jan-23 31-Dec-23 HDFCBANK 1689.75 1650.25 1657 1686.7 1685 1657 18294620 30604491502 1757.5 1460.25 385913 21-Dec-23 1672.87
HDFCBANK 01-Jan-23 31-Dec-23 HDFCBANK 1668.95 1644.45 1658 1657 1644.75 1652.9 17116260 28451245154 1757.5 1460.25 415287 20-Dec-23 1662.23
IREDA 01-Dec-23 31-Dec-23 IREDA 114.2 98.3 102 110.75 111.15 108.3 167648627 18085063262 123.2 50 527506 18-Dec-23 107.87
IREDA 01-Dec-23 31-Dec-23 IREDA 121 108.3 120.4 108.3 108.3 120.3 107529568 12023317404 123.2 50 513417 15-Dec-23 111.81
SBIN 01-Jan-23 31-Dec-23 SBIN 619.9 613.1 615 614.25 614.2 614.15 11706255 7210878841 629.55 499.35 285158 11-Dec-23 615.99
SBIN 01-Jan-23 31-Dec-23 SBIN 616.8 605.35 610 614.15 614.5 611.7 17722136 10822316306 629.55 499.35 294891 08-Dec-23 610.67
SBIN 01-Jan-23 31-Dec-23 SBIN 612.55 605 610 611.7 611.5 608.45 10725706 6539746290 629.55 499.35 248309 07-Dec-23 609.73
SBIN 01-Jan-23 31-Dec-23 SBIN 614 604.55 614 608.45 607.7 608.25 21439069 13076949822 629.55 499.35 273409 06-Dec-23 609.96
SBIN 01-Jan-23 31-Dec-23 SBIN 611.3 595.05 595.05 608.25 608.25 594.7 36430152 22090196336 629.55 499.35 432396 05-Dec-23 606.37
SBIN 01-Jan-23 31-Dec-23 SBIN 596 584.55 588 594.7 595.9 571.75 26077882 15393175008 629.55 499.35 386859 04-Dec-23 590.28
SBIN 01-Jan-23 31-Dec-23 SBIN 574.2 566.55 567 571.75 571.85 564.75 10614230 6069542121 629.55 499.35 189868 01-Dec-23 571.83
SBIN 01-Jan-23 31-Dec-23 SBIN 570.2 563.65 569.75 564.75 565.15 568.6 19758998 11187929537 629.55 499.35 314822 30-Nov-23 566.22
SBIN 01-Jan-23 31-Dec-23 SBIN 569 565.1 568 568.6 568.35 564.45 10299034 5845506569 629.55 499.35 206929 29-Nov-23 567.58
SBIN 01-Jan-23 31-Dec-23 SBIN 565.2 561.1 563.6 564.45 564.8 560.35 8153314 4593727820 629.55 499.35 186090 28-Nov-23 563.42
SBIN 01-Jan-23 31-Dec-23 SBIN 562.45 559.25 561.95 560.35 560.7 559.95 6529851 3660804949 629.55 499.35 155259 24-Nov-23 560.63

We can see there is a huge difference in the price of these 3 stocks, I want to compare them using the Growth % on a Day-on-Day basis.

Here is the measure I created but it isn't working -

'StocksList'[Growth] = VAR currDate = SELECTEDVALUE('StocksList'[QuotePull.Column1.CH_TIMESTAMP])
VAR preDate = CALCULATE(
    MAX('StocksList'[QuotePull.Column1.CH_TIMESTAMP]),
    FILTER(
        'StocksList',
        'StocksList'[QuotePull.Column1.CH_TIMESTAMP] < currDate
    )
)
VAR DoDchange = DIVIDE(
    CALCULATE(
        MAX('StocksList'[QuotePull.Column1.CH_LAST_TRADED_PRICE]),
        'StocksList'[QuotePull.Column1.CH_TIMESTAMP] = currDate
    ) - CALCULATE(
        MAX('StocksList'[QuotePull.Column1.CH_LAST_TRADED_PRICE]),
        'StocksList'[QuotePull.Column1.CH_TIMESTAMP] = preDate
    ),
    CALCULATE(
        MAX('StocksList'[QuotePull.Column1.CH_LAST_TRADED_PRICE]),
        'StocksList'[QuotePull.Column1.CH_TIMESTAMP] = preDate
    ),
    0
)
RETURN
    DoDchange

Not sure what I'm doing wrong probably because all the stock data are in the same table. and I'm creating the line chart using the QuotePull.Column1.CH_SYMBOL column as a legend to differentiate the three. Can anyone help me with the same?

EDIT -

What I'm looking for is a view like this as you can see we are comparing the growth/loss with the earliest value of that particular stock. -

enter image description here

Solution

  • EDIT

    Growth = 
VAR date1 = CALCULATE( MIN(StocksList[QuotePull.Column1.CH_TIMESTAMP]), REMOVEFILTERS(), VALUES(StocksList[StockQuoteTable]) )
VAR date1Price = CALCULATE( SUM( StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE] ), REMOVEFILTERS(), VALUES(StocksList[StockQuoteTable]), StocksList[QuotePull.Column1.CH_TIMESTAMP] = date1 )

VAR change = 
DIVIDE( 
    SUM(StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE]) - date1Price, 
    date1Price, 
    0
)

RETURN change

    EDIT

    EDIT

    Growth = 
VAR currDate = MAX(StocksList[QuotePull.Column1.CH_TIMESTAMP])

VAR preDate = CALCULATE( MIN(StocksList[QuotePull.Column1.CH_TIMESTAMP]), REMOVEFILTERS(), VALUES(StocksList[StockQuoteTable]) )

VAR DoDchange = DIVIDE(CALCULATE( SUM(StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE]), StocksList[QuotePull.Column1.CH_TIMESTAMP] = currDate ) - CALCULATE( MAX( StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE] ), FILTER(ALLSELECTED(StocksList), StocksList[QuotePull.Column1.CH_TIMESTAMP] = preDate )), CALCULATE( MAX( StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE] ), FILTER(ALLSELECTED(StocksList), StocksList[QuotePull.Column1.CH_TIMESTAMP] = preDate )), 0)

RETURN DoDchange

    EDIT

    Does this work?

    Growth = 
VAR currDate = MAX(StocksList[QuotePull.Column1.CH_TIMESTAMP])

VAR preDate = CALCULATE( MAX(StocksList[QuotePull.Column1.CH_TIMESTAMP]), FILTER(ALLSELECTED(StocksList), StocksList[QuotePull.Column1.CH_TIMESTAMP] < currDate ) )

VAR DoDchange = DIVIDE(CALCULATE( SUM(StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE]), StocksList[QuotePull.Column1.CH_TIMESTAMP] = currDate ) - CALCULATE( MAX( StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE] ), FILTER(ALLSELECTED(StocksList), StocksList[QuotePull.Column1.CH_TIMESTAMP] = preDate )), CALCULATE( MAX( StocksList[QuotePull.Column1.CH_LAST_TRADED_PRICE] ), FILTER(ALLSELECTED(StocksList), StocksList[QuotePull.Column1.CH_TIMESTAMP] = preDate )), 0)

RETURN DoDchange