Multiple Date filters for one amount column

[Maescobar]

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:

1. Stacked chart filtered by "MMM/YYYY"
2. 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: 

1. First line = 740.76
2. Second line = 268

[amitchandak]

@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...

[Maescobar]

@amitchandak  

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 😕

[Icey]

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.

[Maescobar]

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. 

Fact Sheet 

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

[Icey]

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

[Maescobar]

@Icey 

I only have permission to save to google drive. Hope this works.

Fact Sheet2 

[Icey]

Hi @Maescobar ,

What's the calculation logic of [Paid Amount]?

Best Regards,

Icey

[Maescobar]

@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

[Icey · Accepted 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.