Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rob_vander
Helper I
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.

AcctDivisionDateValue
1A01/01/2023100
1B02/01/202350
1B03/01/202370
1B05/01/202380
2A03/01/202390
2A04/01/202340
2A05/01/2023110
2B06/01/202360

 

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

AcctDivisionValue
1B70
2A40

 

2 ACCEPTED SOLUTIONS
123abc
Community Champion
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.

View solution in original post

v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1695028476678.png

 

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.

View solution in original post

6 REPLIES 6

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
Helper I
Helper I

@marcorusso Can you help me?

 

 

 

v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1695028476678.png

 

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.

@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

rob_vander
Helper I
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

123abc
Community Champion
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors