Line chart based on selected year in slicer

[sagarp]

I have two different excel sheets with following tables,
1. Inbound

Fiscal Year	Fiscal Week	Domestic	Import
2024	1	12	6
2024	2	13	9
2024	3	10	8
2024	4	15	4

2. Outbound

Fiscal Year	Fiscal Week	Store	Wholesale
2024	1	12	2
2024	2	15	6
2024	3	10	9
2024	4	14	6

I need to make visual of line charts, which display total inbound (import + domestic) and total outbound (store + wholesale) on y-axis (2 different lines) and Fiscal week on x-axis. Now I nut to put the slicer with Fiscal year. So if I pick any fiscal year (ex-2024) then it will diplay line charts of total outbound and invound vs fiscal week of 2024.
I tried to do it, but it just display the data of total inbound if I put fiscal year of Inbound data in Slicer.
Cold you please help me with this?

[Anonymous · Accepted solution]

Hi @sagarp ,

Thanks for the reply from @lbendlin , please allow me to provide another insight: 

Create measure.

In_True =

SUMX ( 'Inbound', [Domestic] ) + SUMX ( 'Inbound', [Import] )

Out_True =

VAR _Store =

    SUMX (

        FILTER (

            ALL ( Outbound ),

            'Outbound'[Fiscal Year] = MAX ( 'Inbound'[Fiscal Year] )

                && 'Outbound'[Fiscal Week] = MAX ( 'Inbound'[Fiscal Week] )

        ),

        'Outbound'[Store]

    )

VAR _Wholesale =

    SUMX (

        FILTER (

            ALL ( Outbound ),

            'Outbound'[Fiscal Year] = MAX ( 'Inbound'[Fiscal Year] )

                && 'Outbound'[Fiscal Week] = MAX ( 'Inbound'[Fiscal Week] )

        ),

        'Outbound'[Wholesale]

    )

RETURN

    _Store + _Wholesale

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[sagarp]

@AnonymousThank you so much for providing the solution. It works perfectly for me. This is exactly what I was looking for.

[lbendlin]

Depends a bit on how large your tables are, but in general you want to unpivot both and then append them.

Then you can create two measures

Inbound = CALCULATE(sum(Combined[Value]),Combined[Attribute] in {"Domestic","Import"})

Outbound = CALCULATE(sum(Combined[Value]),Combined[Attribute] in {"Store","Wholesale"})

and then you can plot both.

[sagarp]

@lbendlinThank you for your response and the solution provided. I actually have fiscal weeks 1 to 52 for 2023, 2024, and 2025. Your solution worked for me, but I decided to use Clara's solution as it is more suitable for my needs.

Thanks again.