Previous Cost of a product for the selected date.

[alamp]

Hi All,

I have  a tabel with Product, cost, quantity,filedate,brand. I want to calculate previous date cost based on the filedate column.

My calculation is not giving me correct result for cost. I have calculated current cost which takes minimum of a product on the selected date for the brand. 

Below is the table. 

Product    Cost  Qty   filedate        Brand

product1 $101  320  31/10/2023  kellogs

product8 $721  80    31/10/2023  kellogs

product2 $1500 50   29/10/2023  parle

product3 $360  150  28/10/2023  parle

product4 $46    980  26/10/2023  kellogs

Below are my measures.

Measure 1

CostMeasureMin =

VAR SelectedDate =

    SELECTEDVALUE ( 'all_disti_og'[filedate] )

VAR TodaysDate =

    TODAY ()

VAR FilteredDate =

    IF ( ISBLANK ( SelectedDate ), TodaysDate, SelectedDate )

VAR CostOnFilteredDate =

    CALCULATE (

        MIN( all_disti_og[cost] ),

        'all_disti_og'[aq_vendor] IN VALUES ( 'all_disti_og'[aq_vendor] )

    )

RETURN

    CostOnFilteredDate

Measure 2

y date =

VAR seldate = SELECTEDVALUE(all_disti_og[filedate])

VAR Prevdate = CALCULATE(

                    MAX(all_disti_og[filedate]),

                    FILTER(

                        ALL(all_disti_og),

                        all_disti_og[filedate] < seldate

                    )

)

VAR Result =

CALCULATE(

    MAX(all_disti_og[filedate]),

    FILTER(ALL(all_disti_og),all_disti_og[filedate] = Prevdate)

)

RETURN

Result

----------------------------------------------

Based on measure 2 i want to get previous cost but could not get it. Then i want to get the cost difference from previous and today.
Need your expertise to resolve it. 

[v-shex-msft]

Hi @alamp ,

Can you please share a pbix or 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

[alamp]

Hi @Dangar332 ,

Thanks for the response. I used the fromula but it is providing total cost for the product. I think i missed one more filter of manufactures. Let me put a small file so that you could guide me better. 

[Dangar332]

Hi, @alamp 

as i understood your problem try below

pre date = MAXX(FILTER(ALL(cost),cost[filedate]<MAX(cost[filedate]) && cost[brand]=MAX(cost[brand])),cost[filedate])


result = 
var a = MAXX(FILTER(ALL(cost),cost[filedate]<MAX(cost[filedate]) && cost[brand]=MAX(cost[brand])),cost[filedate])
return 
SUMX(FILTER(ALL(cost),cost[filedate]=a),cost[cost])