Forum Discussion

MartynasBI's avatar
MartynasBI
Frequent Visitor
3 years ago
Solved

date and days difference

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,

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.