Forum Discussion

tmhalila's avatar
tmhalila
Resolver II
2 years ago
Solved

Get the last value submitted

Hello there, I have a dataset that tracks weekly performance but data are submitted on daily from different branches. I have written a measure that dynamically selects the last record submitted base...
Need Help
Tips and Tricks
  • tmhalila's avatar
    tmhalila
    2 years ago

    Hello,

    I have managed to solve with below functions

    cummulative = 
  VAR SelectedStartDate = MIN (dim_date[date])
  VAR SelectedEndDate = MAX (dim_date[date])

RETURN
    SUMX (
        VALUES('dataset'[branch_id]),
        VAR LatestDate =
            CALCULATE (
                MAX ('dataset'[report_date]),
                'dataset'[branch_id]
                    = EARLIER('dataset'[branch_id])
                    && 'dataset'[report_date] >= SelectedStartDate
                    && 'dataset'[report_date] <= SelectedEndDate
            )
        RETURN
            CALCULATE (
                SUM ('dataset'[cummulative_indicator]),
                'dataset'[branch_id]
                    = EARLIER ( 'dataset'[branch_id])
                    && 'dataset'[report_date] = LatestDate
            )
    )
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Try the below,

 

It gets the first and last date of the selected week from the slicer, and then uses filter to include all dates in this range of dates.

 

cummulative_indicators =
CALCULATE(
SUM('dataset'[cummulative_indicator]),
FILTER(
ALL(dim_date[date]),
dim_date[date] >= MIN(dim_date[date]) && dim_date[date] <= MAX(dim_date[date])
)
)

tmhalila's avatar
tmhalila
Resolver II
2 years ago

I got it from ChatGPT it doesn't work and it gives out the wrong results, And took too long to compute since the actual dataset has more than 5M records.

 

What I am looking for is when "Branch A" submits a report for the week of 27 Nov 2023 - 03 Dec 2023 on 27 and 29 then other "Branches" submit their reports up to 03 For others I will pick all values submitted on 03 which is end of the week but I'll also include the value for "Branch A" on 29.