Forum Discussion
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 |
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 _SUMResult 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
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.
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
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 _SUMResult 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.
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.
