How to calculate the days difference between calendar date and created date

[PBI_newuser]

Hi, I have below data (Table 1) and I want to count the job which open more than 100 days based on the calendar month.

Example:

For Job# = J-111, in Oct 2021, the days diff between the last day of Oct 2021 (10/31/2021) and the Created Date is 102 days.

For J-777, there is a closed date. In Sep 2021, the job is not closed and the days difference should be calculated from 03/06/2021 to 9/30/2021 which is 208 days. In Oct 2021, J-777 is closed and the days difference should be calculated from 03/06/2021 to 10/18/2021 which is 226 days.

Then I want to create a chart to count number of jobs in each month which is more than 100 days.

I do not know how to create the measurse of the days difference.  Here is the pbix file.

Table 1:

Job#	Created Date	Closed Date	... Aug 2021	Days Diff in Sep 2021	Days Diff in Oct 2021	Days Diff in Nov 2021	...
J-111	07/21/2021			71	102	132
J-123	08/09/2021			52	83	113
J-132	09/02/2021			28	59	89
J-222	06/25/2021			97	128	158
J-444	05/06/2021			147	178	208
J-555	11/01/2021			-32	-1	29
J-666	10/10/2021			-10	21	51
J-777	03/06/2021	10/18/2021		208	226	226
J-888	02/08/2021			234	265	295
J-999	01/11/2021	9/11/2021		243	243	243

[v-yanjiang-msft]

Hi @PBI_newuser ,

According to your description, here's my solution.

1.Create a column in calendar table.

MonthYear = FORMAT('Calendar'[Date],"YYYY-MM")

2.Create a measure, I let the negtive number be blank in my formula.

Job Count =
VAR _DIFF =
    DATEDIFF (
        MAX ( 'Table'[Created Date] ),
        COALESCE (
            MAX ( 'Table'[Closed Date] ),
            MAXX (
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                ),
                'Calendar'[Date]
            )
        ),
        DAY
    )
RETURN
    IF (
        _DIFF < 0
            || MAXX (
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                ),
                'Calendar'[Date]
            )
                < MAX ( 'Table'[Created Date] ),
        BLANK (),
        _DIFF
    )

Put Job in rows, Monthyear in columns and Job count in values.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @v-yanjiang-msft , thank you for the solution. How to modify the measure of Job Count to calculate the day difference between last day of the month and created date?

For example, J-777, in the month of 2021-03, the job is not closed, so it should calculate the day difference between 03/06/2021 to 03/31/2021 which is 25 days. 

In 2021-04, it should calculate from 03/06/2021 to 04/30/2021 which is 55 days.

[v-yanjiang-msft]

Hi @PBI_newuser ,

According to your description, I modify my formula like this.

Job Count =
VAR _DIFF =
    IF (
        ISBLANK ( MAX ( 'Table'[Closed Date] ) ),
        DATEDIFF (
            MAX ( 'Table'[Created Date] ),
            MAXX (
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                ),
                'Calendar'[Date]
            ),
            DAY
        ),
        DATEDIFF (
            MAX ( 'Table'[Created Date] ),
            MIN (
                MAX ( 'Table'[Closed Date] ),
                MAXX (
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                    ),
                    'Calendar'[Date]
                )
            ),
            DAY
        )
    )
RETURN
    IF (
        MAXX (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
            ),
            'Calendar'[Date]
        )
            < MAX ( 'Table'[Created Date] ),
        BLANK (),
        _DIFF
    )

Then get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @v-yanjiang-msft , how to stop calculating the day difference after the closed date?

This is because I am calculating how many jobs are not closed in each month. Once it's closed, it shouldn't be counted. For example, day difference in 2021-11 for J-777 should be blank.

Job = CALCULATE(COUNTX('Table',[Job Count]),FILTER('Table',[Job Count]>100))

[v-yanjiang-msft · Accepted solution]

Hi @PBI_newuser ,

According to your description, I modify my formula like this.

Job Count =
VAR _DIFF =
    IF (
        ISBLANK ( MAX ( 'Table'[Closed Date] ) ),
        DATEDIFF (
            MAX ( 'Table'[Created Date] ),
            MAXX (
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                ),
                'Calendar'[Date]
            ),
            DAY
        ),
        DATEDIFF (
            MAX ( 'Table'[Created Date] ),
            MIN (
                MAX ( 'Table'[Closed Date] ),
                MAXX (
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                    ),
                    'Calendar'[Date]
                )
            ),
            DAY
        )
    )
RETURN
    IF (
        ISBLANK ( MAX ( 'Table'[Closed Date] ) ),
        IF (
            MAXX (
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                ),
                'Calendar'[Date]
            )
                < MAX ( 'Table'[Created Date] ),
            BLANK (),
            _DIFF
        ),
        IF (
            MAXX (
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                ),
                'Calendar'[Date]
            )
                < MAX ( 'Table'[Created Date] )
                || MINX (
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[MonthYear] = MAX ( 'Calendar'[MonthYear] )
                    ),
                    'Calendar'[Date]
                )
                    > MAX ( 'Table'[Closed Date] ),
            BLANK (),
            _DIFF
        )
    )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @v-yanjiang-msft , that's what I am expecting! Thank you so much!! 😉

[amitchandak]

@PBI_newuser , assuming this date is coming from a slicer or from a date table

try a measure like

Sumx(Table, Datediff([Created Date], coalesce([close date], max('Date'[Date])),Day))

or

Sumx(values(Table[JOB#], Datediff(Min(Table[Created Date]), coalesce(Max(Table([close date]), max('Date'[Date])),Day))

[PBI_newuser]

Hi @amitchandak , it doesn't work. Here is the pbix file.