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,
One of my clients has given me a table with their spend data. This table holds spend purchasing document data and the purchase orders they have been booked on. Below you will find a sample of this data
For the client it is very important to know the SPEND VALUE for the orders that were recorded AFTER the FIRST document was recorded. Furthermore, they want to limit this value to the orders that were created in the context year.
As you can see in the above table I've already added a column showing which order were created before the first document was created (TRUE) and which were created after the first document was created (FALSE).
Below are the steps I've already taken:
Total Spend = CALCULATE ( SUM ( 'Spend'[Amount] ), USERELATIONSHIP ( 'Spend'[Document date], 'Calendar'[Date] ) )Below you will find the output.
Filter #1 = CALCULATE ( [Total Spend], FILTER ( Spend, Spend[Order before document] = FALSE () ) )Below you will find the output.
Filter #2 = CALCULATE ( [Filter #1], DATESBETWEEN ( Spend[Order date], MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) ) )Below you will find the output.
As you can see the last measure "Filter #2" does not give the correct total. This is because we have a double filter on dates. First on document date (Total Spend measure) and later on on order date (Filter #2). On the total line both of these contexts are dropped, creating an incorrect total.
My question is obvious. What DAX code will calculate the correct total value? I'm thinking about using ISFILTERED, but still can't come up with any working code.
Can you help me?
Regards,
Erwin
Solved! Go to Solution.
Hi Greg,
I already knew I was having this particular problem. Nontheless, you've put me on the right track and I've come up with the code myself (which is always best, I think).
Filter #3 = IF ( HASONEFILTER('Calendar'[Year]), [Filter #2], CALCULATE ( [Filter #2], FILTER ( Spend, YEAR ( Spend[Order date] ) = YEAR ( Spend[Document date] ) ) ) )
Many thanks for your advice.
Rg. Erwin
Hi Greg,
I already knew I was having this particular problem. Nontheless, you've put me on the right track and I've come up with the code myself (which is always best, I think).
Filter #3 = IF ( HASONEFILTER('Calendar'[Year]), [Filter #2], CALCULATE ( [Filter #2], FILTER ( Spend, YEAR ( Spend[Order date] ) = YEAR ( Spend[Document date] ) ) ) )
Many thanks for your advice.
Rg. Erwin
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |