Forum Discussion

santoshlearner2's avatar
santoshlearner2
Resolver II
2 years ago
Solved

Daily Changes from the available dates

Dear All,   I have daily stock company / product / stock quantity and price /on  a daily basis the value subtracts the value from the previous day by a calculated column, instead of a measure, the ...
Need Help
  • PowerBIDave's avatar
    PowerBIDave
    2 years ago

    santoshlearner2

    Assuming your existing table just has columns named Date, Company, Product, Stock, and Price, you could create two calculated columns with the code below to produce the result you want. It may not be the most elegant code but it works. There are potentially better ways of doing this by modelling the data correctly and calculating the values with measures, but if you have only the one table of data and insist on using calculated columns then this should work. Good luck.

     

    ************ First Calculated Column ************

    Stock Change =
    VAR __currentDate = StockTable[Date]
    VAR __currentCompany = StockTable[Company]
    VAR __currentProduct = StockTable[Product]
    VAR __currentStockValue = StockTable[Stock]
    VAR __filteredTable =
    FILTER(
    StockTable,
    StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] > __currentDate
    )
    VAR __nextDate =
    MINX(
    __filteredTable,
    [Date]
    )
    VAR __result =
    IF(
    NOT ISBLANK(__nextDate),
    __currentStockValue -
    MINX(
    FILTER(
    __filteredTable,
    [Date] = __nextDate
    ),
    [Stock]
    )
    )
    RETURN
    __result

     

    ************ Second Calculated Column ************

    Price Change =
    VAR __currentDate = StockTable[Date]
    VAR __currentCompany = StockTable[Company]
    VAR __currentProduct = StockTable[Product]
    VAR __currentPriceValue = StockTable[Price]
    VAR __filteredTable =
    FILTER(
    StockTable,
    StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] > __currentDate
    )
    VAR __nextDate =
    MINX(
    __filteredTable,
    [Date]
    )
    VAR __result =
    IF(
    NOT ISBLANK(__nextDate),
    __currentPriceValue -
    MINX(
    FILTER(
    __filteredTable,
    [Date] = __nextDate
    ),
    [Price]
    )
    )
    RETURN
    __result

     

PowerBIDave's avatar
PowerBIDave
Regular Visitor
2 years ago

santoshlearner2

Assuming your existing table just has columns named Date, Company, Product, Stock, and Price, you could create two calculated columns with the code below to produce the result you want. It may not be the most elegant code but it works. There are potentially better ways of doing this by modelling the data correctly and calculating the values with measures, but if you have only the one table of data and insist on using calculated columns then this should work. Good luck.

 

************ First Calculated Column ************

Stock Change =
VAR __currentDate = StockTable[Date]
VAR __currentCompany = StockTable[Company]
VAR __currentProduct = StockTable[Product]
VAR __currentStockValue = StockTable[Stock]
VAR __filteredTable =
FILTER(
StockTable,
StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] > __currentDate
)
VAR __nextDate =
MINX(
__filteredTable,
[Date]
)
VAR __result =
IF(
NOT ISBLANK(__nextDate),
__currentStockValue -
MINX(
FILTER(
__filteredTable,
[Date] = __nextDate
),
[Stock]
)
)
RETURN
__result

 

************ Second Calculated Column ************

Price Change =
VAR __currentDate = StockTable[Date]
VAR __currentCompany = StockTable[Company]
VAR __currentProduct = StockTable[Product]
VAR __currentPriceValue = StockTable[Price]
VAR __filteredTable =
FILTER(
StockTable,
StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] > __currentDate
)
VAR __nextDate =
MINX(
__filteredTable,
[Date]
)
VAR __result =
IF(
NOT ISBLANK(__nextDate),
__currentPriceValue -
MINX(
FILTER(
__filteredTable,
[Date] = __nextDate
),
[Price]
)
)
RETURN
__result

 

santoshlearner2's avatar
santoshlearner2
Resolver II
2 years ago

Hi,

 

Superb of you, i am getting the values, but the issue is say if i am on 25th June , the value is 25.105 and 24th june the june the value is 25.000, then on 25th june i should get 0.105, but the value of 0.105 is showing for 24th june, it is not showing next to the 25th june date.  I think the previous day measure should be changed, can you help

  • PowerBIDave's avatar
    PowerBIDave
    Regular Visitor
    2 years ago

    santoshlearner2

    You just need to tweak the code I provided previously to get the changes going forward.

    Something along the lines of the following.


    ************ First Calculated Column ************

    Stock Change Going Forward =
    VAR __currentDate = StockTable[Date]
    VAR __currentCompany = StockTable[Company]
    VAR __currentProduct = StockTable[Product]
    VAR __currentStockValue = StockTable[Stock]
    VAR __filteredTable =
    FILTER(
    StockTable,
    StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] < __currentDate
    )
    VAR __prevDate =
    MAXX(
    __filteredTable,
    [Date]
    )
    VAR __result =
    IF(
    NOT ISBLANK(__prevDate),
    __currentStockValue -
    MINX(
    FILTER(
    __filteredTable,
    [Date] = __prevDate
    ),
    [Stock]
    )
    )
    RETURN
    __result


    ************ Second Calculated Column ************

    Price Change Going Forward =
    VAR __currentDate = StockTable[Date]
    VAR __currentCompany = StockTable[Company]
    VAR __currentProduct = StockTable[Product]
    VAR __currentPriceValue = StockTable[Price]
    VAR __filteredTable =
    FILTER(
    StockTable,
    StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] < __currentDate
    )
    VAR __prevDate =
    MAXX(
    __filteredTable,
    [Date]
    )
    VAR __result =
    IF(
    NOT ISBLANK(__prevDate),
    __currentPriceValue -
    MINX(
    FILTER(
    __filteredTable,
    [Date] = __prevDate
    ),
    [Price]
    )
    )
    RETURN
    __result

    ************************************

     

    Hope that helps.

     

    If this answers your question, please mark as a solution so others can find.