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 everyone,
I'm a bit stuck with a report I'm trying to build.
I have the below columns where I'm trying to find for how many days it was outstanding.
I've already created new columns for this in PBI where if outstanding (Status still open and I calculate Report Date - Created date) between 1-4 working days results in "good", if 5-10 working days - "amber" and everything higher is "red"
The problem I have is :
- For example, the invoice with ID 0123, created on 05/06/2023, shows on every report for 05/06, 06/06, 07/06, 08/06 as "good" as in all those days it was created within 4 working day, however on 09/06/2023 it will show as "amber".
Assuming I only have 1 invoice on my report (ID 0123), and if I filter the report for last week, I will see that 1 invoice was "good", 1 invoice was "amber", where I would like to see that last week I only had 1 invoice and that was in "amber"
Essentially if it was good and now amber, I want to see it as amber now based on each distinct invoice ID. If it was good, then amber and now red, see it as red.
I've tried the below formula to create a new column, however I don't know how to integrate this one or other formula in order to include the Distinct of Invoice ID
Hi @Anonymous ,
I have created a simple sample , please refer to my pbix file to see if it helps you.
Create a column first.
weeknum =
WEEKNUM('Table'[Reprt Date],1)
Then create 2 measures.
Measure =
VAR _1 =
DATEDIFF ( MAX ( 'Table'[Invoice Creaated] ), MAX ( 'Table'[Reprt Date] ), DAY )
VAR _2 =
IF (
_1 <= 3
&& MAX ( 'Table'[status] ) = "Open",
"Good",
IF (
_1 <= 9
&& _1 >= 4
&& MAX ( 'Table'[status] ) = "Open",
"Amber",
IF ( MAX ( 'Table'[status] ) = "Open" && _1 > 9, "Red", BLANK () )
)
)
RETURN
SWITCH ( TRUE (), _2 = "Good", 1, _2 = "Amber", 2, _2 = "Red", 3, BLANK () )
result =
VAR _1 =
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[weeknum] = SELECTEDVALUE ( 'Table'[weeknum] )
&& 'Table'[invoice ID] = SELECTEDVALUE ( 'Table'[invoice ID] )
),
[Measure]
)
RETURN
IF ( _1 = 1, "Good", IF ( _1 = 2, "Amber", IF ( _1 = 3, "Red", BLANK () ) ) )
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for taking you time and for your reply.
Unfortunately this didn't solve the issue from multiple considerents.
1 - I will need to take in consideration only working days when deciding if between 1-4 days, 5-10 etc.
2 - I can't use the measure you've created as I will need this to be in a cluster column chart. What I'm trying to achieve is something like below (FYI, below are multiple invoice references, for a specific agent)
The issue with the above, is that Invoice ID0123 for example, was good but now is amber and it still counts as 1 for both of them. If I filter the cluster by that invoice, I get the below, when I would like to have only 1 cluster, saying 1 as Amber (if filtered for ID0123)
I've attached screenshots with raw data and PBI as it doesn't let me attaching them here
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 |
---|---|
111 | |
94 | |
92 | |
84 | |
69 |
User | Count |
---|---|
162 | |
129 | |
126 | |
106 | |
98 |