Complex Calculation help required

[rob_vander]

Hi All,

I have below Data set. If I select particular date (here format is DD/MM/YYYY) from slicer, I need to go back till very first date for individual account and pick up Division and Value.

Acct	Division	Date	Value
1	A	01/01/2023	100
1	B	02/01/2023	50
1	B	03/01/2023	70
1	B	05/01/2023	80
2	A	03/01/2023	90
2	A	04/01/2023	40
2	A	05/01/2023	110
2	B	06/01/2023	60

If I select  date '04/01/2023' from slicer, for Acct =1, it will scan all the date (<= 04/01/2023) , will pick up Division & Value which will be 'B' & 70 on '03/01/2023' (because for this Acct, there is no entry for '04/01/2023' so pick up most recent)

for Acct =2, it will scan all the date (<= 04/01/2023), will pick up Division & Value which will be 'A' & 40 on '04/01/2023' 

So basically for each account , I need to pick up recent Division & Value based on selected Date. Challenge is, recent date could be different for different account, in some case if I select Date '01/01/2023', recent entry could be 6 months back

So I want to generate report

Acct	Division	Value
1	B	70
2	A	40

[123abc · Accepted solution]

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.

[marcorusso]

You already have other answers in this thread - if you do not want to change the data model, it's hard to help. You should have at least a separate date table; if it's connected, you can always disable the filter propagation in the measure, but why did you not reply to @123abc ? I didn't read in detail but the approach seems right.

[rob_vander]

@marcorusso Can you help me?

[Anonymous · Accepted solution]

Hi @rob_vander ,

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.

[rob_vander]

@Anonymous  I cannot create un related table as based on date selection other report should also work. Also, when date is not selected, calculation should work based on max date date available for each account

[rob_vander]

@123abc Thanks for the response. Acct is actually text values in real data. Hence, max won't work. Also, Division and Value should be seprate column in report

[123abc · Accepted solution]

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.