cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Complex Calculation help required

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

2 ACCEPTED SOLUTIONS
Community Champion

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.

Community Support

Hi @rob_vander ,

``````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.

6 REPLIES 6
MVP

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.

Helper I

@marcorusso Can you help me?

Community Support

Hi @rob_vander ,

``````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.

Helper I

@v-rzhou-msft  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

Helper I

@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

Community Champion

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.