Forum Discussion
Helper IMultiple Date filters for one amount column
Hey everyone,
I think my previous post was marked as spam for some reason. Anyway, is there anyone who can help me achieve the result below?
I have a set of data with info on invoices: their accrued date, payment date and gross amount.date,
| Date Accrual | Date Payment | Gross Amount |
| 01/04/2019 | 26/09/2019 | 169.56 |
| 01/04/2019 | 25/07/2019 | 165.18 |
| 01/04/2019 | 27/05/2019 | 138.02 |
| 01/04/2019 | 25/04/2019 | 268 |
| 01/08/2018 | 31/08/2018 | 238.4 |
| 01/08/2018 | 26/11/2018 | 92.14 |
| 01/08/2018 | 21/12/2018 | 633.83 |
| 01/08/2018 | 27/05/2019 | 19.72 |
| 01/12/2018 | 28/12/2018 | 80.4 |
| 01/12/2018 | 21/12/2018 | 187.91 |
| 01/12/2018 | 21/12/2018 | 92.32 |
I want to build a Dashboard using the following:
- Stacked chart filtered by "MMM/YYYY"
- Two values: i) SUM of the gross amount accrued and ii) SUM of the gross amount paid BOTH with the same MMM/YYYY filter
Example:
Filter = Apr/2019, results in:
- First line = 740.76
- Second line = 268
Hi Maescobar ,
Please check:
Payment = CALCULATE ( SUM ( 'Table'[GrossAmount] ), USERELATIONSHIP ( Dates[Date], 'Table'[Date Payment] ), FILTER ( 'Table', 'Table'[Date Accrued] IN VALUES ( Dates[Date] ) ) )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
- amitchandak
Super User
Maescobar , You can create a common Date dimension and Join both dates with it. One will be active and another will be inactive. You can active it the relation using userelation :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
MaescobarHi, thanks for taking your time. Congrats on this solution. It show a real world complex problem solution.
I have tried this using "userelationship" but it only gives me how much I paid in a given month AND how much was accrued in the same month.
What I wanted is how much of the accrued month I paid inside the accrued month 😕
- Icey
Community Support
Hi Maescobar ,
Is this what you want?
Please let me know the calculation principles of "Gross Amount Accrued" and "Gross Amount Paid".
What I create in Power BI is something like so:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
