date and days difference

[MartynasBI]

Hi,

I have the following table in Excel

I have the same data for company_id, period, report_issue_date, report_issue_day of week in Power BI.

I would like to calculate the date_diff and days_diff columns in Power BI.

(please note that both columns should reflect the same company_id and should measure the difference from the same period last year)

Thanks,

[v-xinruzhu-msft · Accepted solution]

Hi @MartynasBI 

You can create the following two calculated column

date_diff =
VAR a =
    ADDCOLUMNS (
        'Table',
        "year", CONVERT ( LEFT ( [period], 4 ), INTEGER ),
        "Quarter", CONVERT ( RIGHT ( [period], 1 ), INTEGER )
    )
VAR b =
    MAXX (
        FILTER (
            a,
            [conpany_id] = EARLIER ( 'Table'[conpany_id] )
                && [year]
                    = CONVERT ( LEFT ( EARLIER ( 'Table'[period] ), 4 ), INTEGER ) - 1
                && [Quarter] = CONVERT ( RIGHT ( EARLIER ( [period] ), 1 ), INTEGER )
        ),
        [report_issue_date ]
    )
RETURN
    DATEDIFF ( b, [report_issue_date ], DAY )

days_diff =
VAR a =
    ADDCOLUMNS (
        'Table',
        "year", CONVERT ( LEFT ( [period], 4 ), INTEGER ),
        "Quarter", CONVERT ( RIGHT ( [period], 1 ), INTEGER )
    )
VAR b =
    MAXX (
        FILTER (
            a,
            [conpany_id] = EARLIER ( 'Table'[conpany_id] )
                && [year]
                    = CONVERT ( LEFT ( EARLIER ( 'Table'[period] ), 4 ), INTEGER ) - 1
                && [Quarter] = CONVERT ( RIGHT ( EARLIER ( [period] ), 1 ), INTEGER )
        ),
        [report_issue_day]
    )
RETURN
    IF ( b <> BLANK (), [report_issue_day] - b )

Output

Best Regards!

Yolo Zhu

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