Get the last value submitted

[tmhalila]

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 based on the week selected from the slicer.

cummulative_indicators = 
CALCULATE(SUM('dataset'[cummulative_indicator]),LASTDATE(dim_date[date])
)

The only challenge here I am missing those branches that submitted data at the beginning or in the middle of the reporting week but not on the last date of the week. Example a branch with branch ID 105864-3

Here is the Power BI project your support is appreciated.

[tmhalila · Accepted solution]

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
            )
    )

[Jamie_Scott]

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]

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.