Reporting the end of month status of a job that spans several months

[JohnAnderson_]

Hi all,

I have to report historically on the number of job actions by month split by whether they are overdue or not.

As an example, if one action is created in March, becomes overdue in May and is closed before end of June

Created	Due	Completed
9/03/2022	16/05/2022	17/06/2022

I need to summarise it as follows.  Noting that although the action is still open in June (6th month) it has been closed before the last day of the month.  A daily summary would show this as becoming overdue on the 17/05/2022.

End of Month	Overdue	Value
31/03/2022	No	0
30/04/2022	No	0
31/05/2022	Yes	1

my schema is below, with the odsiAudit actions table containing the 3 date columns.

Do you have any suggestions about how I could produce a monthly summary of the status of the action?

Thank you,

John.

[v-kkf-msft]

Hi @JohnAnderson_ ,

Please create an independent date table.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
    "MonthStart", EOMONTH ( [Date], -1 ) + 1,
    "MonthEnd", EOMONTH ( [Date], 0 )
)

Then create these measures.

Overdue = 
IF (
    MAX ( 'odsiAudit actions'[created_date] ) <= MAX ( 'Calendar'[MonthEnd] )
        && MAX ( 'odsiAudit actions'[due_date] ) > MAX ( 'Calendar'[MonthEnd] ),
    "No",
    IF (
        MAX ( 'Calendar'[MonthEnd] ) >= MAX ( 'odsiAudit actions'[due_date] )
            && MAX ( 'Calendar'[MonthStart] ) < MAX ( 'odsiAudit actions'[completed_date] ),
        "Yes"
    )
)

Value = 
SWITCH ( [Overdue], "No", 0, "Yes", 1 )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JohnAnderson_]

Hello Winniz @v-kkf-msft,

Thank you for this and for the attachment.  Its very close and the logic seems to be working, giving both a literal value and a numeric.  A couple of questions please.

• How does this draw a relationship between the two tables you have created when there is no obvious join?  is the measure applying this relationship on the fly when it is used in the pivot table?
• Is there a way to calculate the total (sum(Value)) so that the total number of overdue and not overdue can be shown in the pivot?  This would result in only 2 columns.

thanks again.

John.

[amitchandak]

@JohnAnderson_ , Create an independent date table and then create a measure like

countx(filter(Table, Table[Created]<= Max(Date[Date]) && Table[Due] <= Max(Date[Date])  && Table[Completed] >= Max(Date[Date]) ), Table[Created])

Use Date from date table , You can create a end of month date like

eomonth([Date],0)

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[JohnAnderson_]

Hi @amitchandak , 

thank you for this.  I'm not sure is working and have re-constructed this in a pbi file, if you have the time.  you will see that when I select the month of March 2021 (31/3/2021) it has a total of 2 but the filtered list below shows more.   {actually not sure how to upload an attachment}.  here is a dropbox link:  https://www.dropbox.com/s/eiyim8plsle6z77/Amit%20solution.pbix?dl=0

John.