Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, I have a question regarding an combined IF-statement or maybe a swich if that is more efficient. I have this invoice data with following important collomns for this calculation (will also provide some sample data at the bottom):
issue_date = date invoice was sent
due_date = date when invoice has to be paid
paid_at = date when client has paid
paid = IF(fact_invoices[paid_at] == "", "NO", "YES")
Paid on time? = IF(fact_invoices[paid_date] < fact_invoices[due_date], "YES", "NO")
Then I have a column "Open & late" with following possibilities:
Already paid: IF(fact_invoices[paid_at] == "", "NO", "YES")
Not yet paid & late: IF(TODAY() > fact_invoices[due_date]
Not yet paid but customer has time: IF(TODAY() < fact_invoices[due_date]
In one formula this looks liks: IF(fact_invoices[Paid] == "YES", "Already Paid", IF(TODAY() > fact_invoices[due_date], "Not yet paid & Late", "Not yet paid but customer has time"))
Already paid: return all values where fact_invoices[paid_at] IS NOT NULL
Not paid: return all values where fact_invoices[paid_at] is empty
Already paid & on time: return all values where fact_invoices[paid_at] IS NOT NULL & fact_invoices[paid_at] < fact_invoices[due_date]
Already late & too late: return all values where fact_invoices[paid_at] IS NOT NULL & fact_invoices[paid_at] > fact_invoices[due_date]
Not yet paid & late: IF(TODAY() > fact_invoices[due_date]
Not yet paid but customer has time: IF(TODAY() < fact_invoices[due_date]
Sample data
What I want to avoid is the use of too many filters:
Probably best to use SWITCH I guess but that is very new to me. Or if there is like a totally better way to di this, please let me know! I wish I could add a .pbix file but it is full of confidential information and I don't know how to get an excel from this. If anything is unclear let me know.
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous ,
Please check:
1. Create Columns.
Paid? = IF ( fact_invoices[paid_at] <> BLANK (), "Already paid", "Not paid" )
On time or late =
IF (
fact_invoices[paid_at] <> BLANK ()
&& fact_invoices[paid_at] <= fact_invoices[due_date],
"Already paid & on time",
IF (
fact_invoices[paid_at] <> BLANK ()
&& fact_invoices[paid_at] > fact_invoices[due_date],
"Already late & too late",
IF (
TODAY () > fact_invoices[due_date],
"Not yet paid & late",
IF ( TODAY () < fact_invoices[due_date], "Not yet paid but customer has time" )
)
)
)
2. Create a Paid Filter table.
Paid Filter =
UNION (
VALUES ( fact_invoices[Paid?] ),
VALUES ( fact_invoices[On time or late] )
)
3. Create a Measure.
Filter Measure =
SWITCH (
TRUE (),
SELECTEDVALUE('Paid Filter'[Paid?])=BLANK(),1,
SELECTEDVALUE ( 'Paid Filter'[Paid?] ) IN VALUES ( fact_invoices[Paid?] ), IF (
MAX ( fact_invoices[Paid?] ) = SELECTEDVALUE ( 'Paid Filter'[Paid?] ),
1
),
SELECTEDVALUE ( 'Paid Filter'[Paid?] )
IN VALUES ( fact_invoices[On time or late] ), IF (
MAX ( fact_invoices[On time or late] ) = SELECTEDVALUE ( 'Paid Filter'[Paid?] ),
1
)
)
4. Put the measure above on the visuals you want to filter and set it =1.
5. Test.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |