Show latest value on each day

[PowerBI_RD]

Hi there,

I've already looked around the forum for a similar issue, and even though I found some that are somewhat like mine, it didn't help me solve it. So here goes.

I'm tracking Stages in the sales process, and every time a Stage changes in Salesforce, I want to calculate the new value of this Stage (each stage has a different percentage associated with it).

As a result I want to show the value on each day/week for all open opportunities, but only for the latest Stage. So whenever a Stage changes from Negotiation to Closed Won for instance, I only want to see the value at the Closed Won row, since the Negotiation row would no longer be relevant/valid.

Currently I use a measure that gives me this as a result:

It is updating the values every time a Stage change occurs, however it is summing up the total and thus also including the previous Stages. Instead of only looking at the latest Stage.

What I would want to see in the above table, is only the value for the latest Stage, but that value has to remain valid for future dates, unless the Stage changes again.

I use a date table for the dates. The measure that I used to generate the above result:

Total Opportunities Weighted Cumulative =

VAR LastDayAvailable =

    CALCULATE (

        MAX ( 'Date_table OppFieldHistory'[Date] )

    )

VAR CurrentDates =

    FILTER (

        VALUES ( 'Date_table OppFieldHistory'[Date] ),

        'Date_table OppFieldHistory'[Date] <= LastDayAvailable

    )

VAR Result =

    CALCULATE([Total Opportunities Weighted Amount OppFieldHistory],

        DATESYTD ( CurrentDates )

    )

RETURN Result

I have managed to visualize the result I want in a different report, and even though I copied most of the measure used there, it's not giving me the result I want.

Any help would be highly appreciated.

Here is a link to my pbix file with dummy data: https://drive.google.com/file/d/1DFMUdzBiFmTvxHvsf-t-kcMKPeXgNfdI/view?usp=sharing

To be completely clear I have attached a screenshot of the result I'm after for the specific OpportunityID that is selected in the pbix file.

[amitchandak]

@PowerBI_RD , Assume created date and date(only date) column, then sum of latest is

Last Qty = Var _max = minx(filter( ALLSELECTED(Data1), Data1[Date] = max(Data1[Date]) ),Data1[Created Date])
return
CALCULATE(countdistinct(Data1[qty]), filter( (Data1), Data1[Date] = max(Data1[Date]) && Data1[Created Date] =_max))

Sum Last Qty = Sumx(VALUES(Data1[Date]) , [Last Qty])

On Sum Last qty you can use datesytd , but use a date table in such a case

calculate([Sum Last Qty], datesytd('Date'[Date]) )

[PowerBI_RD]

Thanks for your response Amit. I found your post about this, but there you used sum instead of distinctcount:

Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[ID] = max(Data1[ID]) ),Data1[Date])
return 
CALCULATE(sum(Data1[qty]), filter( (Data1), Data1[ID] = max(Data1[ID])  && Data1[Date] =_max))Sum Last Qty = sumx(VALUES(Data1[ID]) , [Last Qty])

I have tried your latest suggestion but either I don't understand what to put where, or I'm just doing something wrong. Did you look at my dummy file?