Complex Calculation help required

To achieve the desired result of picking the most recent Division and Value for each account based on the selected date in Power BI, you can use DAX functions. Here's a step-by-step guide on how to do this:

Assuming you have a slicer that allows the user to select a date, create the following measures:

1. **Selected Date:**
Create a measure to capture the selected date from the slicer. Let's call it "SelectedDate."

```DAX
SelectedDate = MAX(SlicerTableName[Date])
```

Replace "SlicerTableName" with the actual name of the table where your slicer data is.

2. **Calculate the Recent Division and Value:**
Now, create a measure to calculate the recent Division and Value for each account based on the selected date.

```DAX
RecentDivisionAndValue =
VAR SelectedAccount = MAX('YourTable'[Acct])
VAR MaxDateForAccount = CALCULATE(MAX('YourTable'[Date]), FILTER('YourTable', 'YourTable'[Acct] = SelectedAccount && 'YourTable'[Date] <= [SelectedDate]))
RETURN
CALCULATE(
VALUES('YourTable'[Division]),
FILTER('YourTable', 'YourTable'[Acct] = SelectedAccount && 'YourTable'[Date] = MaxDateForAccount)
) & " - " & CALCULATE(
SUM('YourTable'[Value]),
FILTER('YourTable', 'YourTable'[Acct] = SelectedAccount && 'YourTable'[Date] = MaxDateForAccount)
)
```

Replace 'YourTable' with the name of your table containing the dataset.

3. **Display the Result:**
Create a table visual in your report and add the "Acct" column along with the "RecentDivisionAndValue" measure to it.

Now, when you select a date from the slicer, the table visual will display the recent Division and Value for each account based on the selected date. The measure calculates the most recent date for each account within the selected date range and then retrieves the Division and Value corresponding to that date.

This approach should generate the report you described with the recent Division and Value for each account based on the selected date.

Hi Anonymous ,

I suggest you to create an unrelated Calendar table to help your calculation.

Value from Recent Date =
VAR _SELECTVALUE =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _MAXDATE =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Acct] ), 'Table'[Date] <= _SELECTVALUE )
    )
VAR _SUM =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Date] = _MAXDATE )
    )
RETURN
    _SUM

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.