Measure over due invoices over time

[ArtNo1000]

Hi 

I want to build a report to measure how much over due invoices we have had a time. I'm new to DAX and have figured out how to make an accumulated measure by filter the calender table, however I cant figure out how make a new filter after that for the Invoice-table. Any suggestions?

My table looks like this and I want the result below

Invoice	Invoice Date	Due Date	Paid Date	Value
1	2019-01-01	2019-01-31	2019-05-15	100
2	2019-02-01	2019-03-03	2019-02-28	100
3	2019-03-01	2019-03-31		100
4	2019-04-01	2019-05-01		100
5	2019-05-01	2019-05-16	2019-06-05	100
6	2019-06-01	2019-07-01		100

Invoice	2019-01	2019-02	2019-03	2019-04	2019-05	2019-06
1		100	100	100
2
3				100	100	100
4					100	100
5					100
6
Total overdue	0	100	100	200	300	200

[v-lid-msft · Accepted solution]

Hi @ArtNo1000 ,

We can use the following measure to meet your requirement:

Measure Value = 
SUMX (
    DISTINCT ( 'Table'[Invoice] ),
    IF(CALCULATE(MAX('Table'[Paid Date])) = BLANK() && MIN('Calendar'[Date]) >= CALCULATE(MIN('Table'[Due Date])),CALCULATE(SUM('Table'[Value]))
    ,IF(NOT(OR(MAX('Calendar'[Date])<=CALCULATE(MIN('Table'[Due Date])),MIN('Calendar'[Date]) >= DATE(YEAR(CALCULATE(MAX('Table'[Paid Date]))),MONTH(CALCULATE(MAX('Table'[Paid Date]))),1)-1)),CALCULATE(SUM('Table'[Value])),BLANK()))
    
)

Best regards,

[amitchandak]

Refer to blog on similar line:

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Greg_Deckler]

Check out Open Tickets. It provides a way of determining a calculation by time period given two dates (start and end date). 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147