Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I have looked extensively for help on this item and have been unable to find something similar.
What I want to do is be able to sort out from a table a specific set of rows according to a set of criteria using a measure. I have compiled all of this information into a single table in BI. As you can see in my example table below, I have accepted and declined quotes with date stamps. I want to take only one estimate in the last 6 months. If there is more then one, I want to exclude the others from my final result. Within that, I want to only take accepted estimates as priority, and if they are all declined, then take the most recent item. The end goal really is to only have one estimate for each car number in a 6 month timeframe.
DATE | ACCEPTED/DECLINED | ESTIMATE NUMBER | Car Number | AMOUNT $ |
1/2/2018 | ACCEPTED | 11 | 445 | 100 |
6/25/2018 | ACCEPTED | 12 | 789 | 150 |
1/29/2019 | DECLINED | 13 | 122 | 125 |
2/2/2019 | DECLINED | 14 | 122 | 105 |
7/10/2020 | DECLINED | 15 | 445 | 90 |
8/13/2020 | ACCEPTED | 16 | 445 | 100 |
9/3/2020 | DECLINED | 17 | 445 | 150 |
The result should look something like this
DATE | ACCEPTED/DECLINED | ESTIMATE NUMBER | Car Number | AMOUNT $ |
1/2/2018 | ACCEPTED | 11 | 445 | 100 |
6/25/2018 | ACCEPTED | 12 | 789 | 150 |
2/2/2019 | DECLINED | 14 | 122 | 105 |
9/3/2020 | ACCEPTED | 16 | 445 | 100 |
Thanks for the help. Feel free to ask clarifying questions if needed.
Solved! Go to Solution.
Hi,
This was a nice one! Please see below how it works. You can select a date, which is used to define the time frame of 6 months. It will then look for accepted quotes, if it does not find any (coalece), it will take the most recent declined quote. Enjoy!
Please note; the date table is unrelated!
The DAX code:
_Filter =
VAR _selCurrentDate =
SELECTEDVALUE ( DateDim[Date] )
VAR _6monthprior =
EDATE ( _selCurrentDate, -6 )
RETURN
IF (
NOT ( ISBLANK ( SUM ( 'Table'[AMOUNT $] ) ) ),
COALESCE (
CALCULATE (
COUNT ( 'Table'[ACCEPTED/DECLINED] ),
FILTER (
'Table',
[DATE].[Date] > _6monthprior
&& [DATE].[Date] <= _selCurrentDate
&& [ACCEPTED/DECLINED] = "ACCEPTED"
)
),
VAR _SELcar =
SELECTEDVALUE ( 'Table'[Car Number] )
VAR _MaxDateDeclined =
CALCULATE (
MAX ( 'Table'[DATE].[Date] ),
FILTER (
ALL ( 'Table' ),
[Car Number] = _SELcar
&& [DATE].[Date] > _6monthprior
&& [DATE].[Date] <= _selCurrentDate
&& [ACCEPTED/DECLINED] = "DECLINED"
)
)
RETURN
CALCULATE (
COUNT ( 'Table'[ACCEPTED/DECLINED] ),
FILTER ( 'Table', [DATE].[Date] = _MaxDateDeclined && [Car Number] = _SELcar )
)
),
BLANK ()
)
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi,
This was a nice one! Please see below how it works. You can select a date, which is used to define the time frame of 6 months. It will then look for accepted quotes, if it does not find any (coalece), it will take the most recent declined quote. Enjoy!
Please note; the date table is unrelated!
The DAX code:
_Filter =
VAR _selCurrentDate =
SELECTEDVALUE ( DateDim[Date] )
VAR _6monthprior =
EDATE ( _selCurrentDate, -6 )
RETURN
IF (
NOT ( ISBLANK ( SUM ( 'Table'[AMOUNT $] ) ) ),
COALESCE (
CALCULATE (
COUNT ( 'Table'[ACCEPTED/DECLINED] ),
FILTER (
'Table',
[DATE].[Date] > _6monthprior
&& [DATE].[Date] <= _selCurrentDate
&& [ACCEPTED/DECLINED] = "ACCEPTED"
)
),
VAR _SELcar =
SELECTEDVALUE ( 'Table'[Car Number] )
VAR _MaxDateDeclined =
CALCULATE (
MAX ( 'Table'[DATE].[Date] ),
FILTER (
ALL ( 'Table' ),
[Car Number] = _SELcar
&& [DATE].[Date] > _6monthprior
&& [DATE].[Date] <= _selCurrentDate
&& [ACCEPTED/DECLINED] = "DECLINED"
)
)
RETURN
CALCULATE (
COUNT ( 'Table'[ACCEPTED/DECLINED] ),
FILTER ( 'Table', [DATE].[Date] = _MaxDateDeclined && [Car Number] = _SELcar )
)
),
BLANK ()
)
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
@stevedepThank you so much for the help. You are directionally where I need to be headed. I think slight tweaks will to your outline will work for me. I hadn’t even thought to break it down with date VARs. That really helped to go line by line as needed. I know it was a bit of a complex problem, but you nailed it. Thanks again.
Welcome! I enjoy the complex problems the most 🙂
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Your criteria are ambiguous for car 445. Do you want to see all accepted quotes or only the latest one?
"The end goal really is to only have one estimate for each car number in a 6 month timeframe."
how do you define the boundaries of that 6 month timeframe?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |