Forum Discussion

tmhalila's avatar
tmhalila
Icon for Resolver II rankResolver 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 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.

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

3 Replies

  • 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
      Icon for Resolver II rankResolver 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.

  • tmhalila's avatar
    tmhalila
    Icon for Resolver II rankResolver II
    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
            )
    )