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.
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:
Example:
Filter = Apr/2019, results in:
Solved! Go to Solution.
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.
@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-tr...
Hi, 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 😕
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.
Hi @Icey
Thanks for sharing. Somehow my sample on the post might have led us to produce wrong results.
I am sharing a more populated database.
When I filter July/2019 and August/2019, I am expecting the following results
Month | Accrued Amount | Paid Amount |
Jul-19 | 2,929,327.70 | 1,525,099.44 |
Aug-19 | 2,252,308.04 | 1,956,810.80 |
Hi @Maescobar ,
Sorry, I have no right to agree here. Please share me the file using other tools, like OneDrive for Business.
Best Regards,
Icey
It is to know: out of the total accrued amount for month MMM/YYYY, how much was paid inside that same MMM/YYYY?
If you filter the fact sheet attached in the previous message, it shuold return the values below:
Month | Accrued Amount | Paid Amount |
Jul-19 | 2,929,327.70 | 1,525,099.44 |
Aug-19 | 2,252,308.04 | 1,956,810.80 |
thanks
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.
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |