Retrieve latest value as of point of time

[satubuku83]

Hi all,

Assuming I have dim_date (containng date_id, date, day, month, year) and dim_claim(claim_id, claim_no) and fact_claim(date_id, claim_id, reserve_amt).

Claim_ID	Date_ID	Reserve_AMT
3	3007	4719.25
1	3038	4289.72
2	3038	13455.6
2	3068	17654.2

How can i write the dax to get only point in time value, for example,

If i build a line chart/time series:

March(date_id 3007) will show 4719.25

April (date_id 3038) will show 4719.25 + 4289.72 + 13455.6

May (date_id 3068) will show 4719.25 + 4289.72 + 17654.2

Regards

[Ashish_Mathur]

Hi,

Share data that can be copied in an Excel file.  Also, how does one interpret 3038? 

[v-frfei-msft · Accepted solution]

Hi @satubuku83,

I made one sample for your reference. Here we can create a measure like this.

Measure =
CALCULATE (
    SUM ( Table1[Reserve_AMT] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Date_ID] <= SELECTEDVALUE ( Table1[Date_ID] )
            && Table1[Date_ID]
                = CALCULATE (
                    MAX ( Table1[Date_ID] ),
                    FILTER (
                        ALLEXCEPT ( Table1, Table1[Claim_ID] ),
                        Table1[Date_ID] <= SELECTEDVALUE ( Table1[Date_ID] )
                    )
                )
    )
)

For more details, please check the pbix as attached.

Regards,

Frank