Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
sagarp
Regular Visitor

Line chart based on selected year in slicer

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

Fiscal YearFiscal WeekDomesticImport
20241126
20242139
20243108
20244154

 

2. Outbound

Fiscal YearFiscal WeekStoreWholesale
20241122
20242156
20243109
20244146

 

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

vkaiyuemsft_0-1716967304941.png

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.

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

vkaiyuemsft_0-1716967304941.png

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.

 

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

lbendlin
Super User
Super User

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

 

lbendlin_0-1716942636618.png

 

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.

 

lbendlin_1-1716942897388.png

 

 

 

@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.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.