Compare selected week days with similar days from the past periods

[ram1212]

Hi,
I need some help how to create similar report with Power BI. I have no idea how to create such measures and such a report.

If I select one week in a filter, for example 2022.09.19-09.25,
then I should get 3 tables below - the exact output as you can see in my picture of excel sheet.
1st table - comparision of the selected and previous week
2nd table - comparision of the same periods of the previous year
3rd table - comparision of the same periods of the selected and previous year

If there are no data for some Date/Facility then the output in the tables should be 0 or 0%.

If no week or more weeks are selected then report should show no data.

My test pbix 

[ram1212 · Accepted solution]

Soved it myself -  joined current and previous week tables, created grouped table and measures

[Anonymous]

Hi @ram1212 ,

Based on the information you provided, you can follow these steps:

1.Add new column.

(1)

2022 =

IF (

    'FactTable'[Date] >= DATE ( 2022, 9, 19 )

        && 'FactTable'[Date] <= DATE ( 2022, 9, 25 ),

    "09.19-09.25d",

    "Previous week"

)

(2)

total =

'FactTable'[IN] + 'FactTable'[OUT]

2.Add three new columns.

(1)

difference_in =

VAR _09 =

    CALCULATE (

        SUM ( FactTable[IN] ),

        FILTER (

            FactTable,

            FactTable[Facility] = EARLIER ( FactTable[Facility] )

                && FactTable[2022] = "09.19-09.25d"

        )

    )

VAR _pre =

    CALCULATE (

        SUM ( FactTable[IN] ),

        FILTER (

            FactTable,

            FactTable[Facility] = EARLIER ( FactTable[Facility] )

                && FactTable[2022] = "Previous week"

        )

    )

RETURN

    ( _09 / _pre ) - 1

(2)

difference_out =

VAR _09 =

    CALCULATE (

        SUM ( FactTable[OUT] ),

        FILTER (

            FactTable,

            FactTable[Facility] = EARLIER ( FactTable[Facility] )

                && FactTable[2022] = "09.19-09.25d"

        )

    )

VAR _pre =

    CALCULATE (

        SUM ( FactTable[OUT] ),

        FILTER (

            FactTable,

            FactTable[Facility] = EARLIER ( FactTable[Facility] )

                && FactTable[2022] = "Previous week"

        )

    )

RETURN

    ( _09 / _pre ) - 1

(3)

difference_TOTAL =

VAR _09 =

    CALCULATE (

        SUM ( FactTable[total] ),

        FILTER (

            FactTable,

            FactTable[Facility] = EARLIER ( FactTable[Facility] )

                && FactTable[2022] = "09.19-09.25d"

        )

    )

VAR _pre =

    CALCULATE (

        SUM ( FactTable[total] ),

        FILTER (

            FactTable,

            FactTable[Facility] = EARLIER ( FactTable[Facility] )

                && FactTable[2022] = "Previous week"

        )

    )

RETURN

    ( _09 / _pre ) - 1

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Ada Wang}

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

[ram1212]

I updated my first message with pbix file link 

[ram1212]

Hi,

Thank you for your answer.

I updated my first message with more details