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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
abhi_kothekar
Frequent Visitor

Merge two line chart with different timeline

I have a despatch plan from Jan to May & also the cash received data from Feb to Jun (Assuming 30 days credit period post despatch). Now in actual it has happened that some of the despatches of Apr month may get preponed and despatched in Feb. So the cash received for those items is at actual i.e. Desp Date + 30 Days. i.e. in Mar.  Now if I want to plot both the graphs on cumulative value the time scale is different for both the values. So how to show them under one chart

MonthDesp PlanCumulative Desp
Jan2020
Feb1535
Mar3065
Apr 2590
May10100
TOTAL100 
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @abhi_kothekar ,

 

Here are the steps you can follow:

1. Create calculated table.

Table2 =
CALENDAR(
    DATE(
        2023,1,1),
        DATE(
            2023,12,31))

vyangliumsft_0-1683103141634.png

2. Create calculated table.

True =
var _table1=
SUMMARIZE(
    'Table2','Table2'[Year],'Table2'[Month],
    "Cumulative1",
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[Desp Plan])='Table2'[Year]&&
    MONTH('Table'[Desp Plan])='Table2'[Month]),[Planned Value]),
    "Cumulative2",
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[Desp Actual])='Table2'[Year]&&
    MONTH('Table'[Desp Actual])='Table2'[Month]),[Desp value]))
var _table2=
ADDCOLUMNS(
    _table1,"Cumulative Plann",
    IF(
        [Cumulative1]=BLANK(),BLANK(),
    SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative1])))
return
ADDCOLUMNS(
    _table2,"Cumulative Desp",
     IF(
        [Cumulative2]=BLANK(),BLANK(),   
    SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative2])))

vyangliumsft_1-1683103141636.png

3. Result:

vyangliumsft_2-1683103200798.png

 

Best Regards,

Liu Yang

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

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @abhi_kothekar ,

 

Here are the steps you can follow:

1. Create calculated table.

Table2 =
CALENDAR(
    DATE(
        2023,1,1),
        DATE(
            2023,12,31))

vyangliumsft_0-1683103141634.png

2. Create calculated table.

True =
var _table1=
SUMMARIZE(
    'Table2','Table2'[Year],'Table2'[Month],
    "Cumulative1",
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[Desp Plan])='Table2'[Year]&&
    MONTH('Table'[Desp Plan])='Table2'[Month]),[Planned Value]),
    "Cumulative2",
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[Desp Actual])='Table2'[Year]&&
    MONTH('Table'[Desp Actual])='Table2'[Month]),[Desp value]))
var _table2=
ADDCOLUMNS(
    _table1,"Cumulative Plann",
    IF(
        [Cumulative1]=BLANK(),BLANK(),
    SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative1])))
return
ADDCOLUMNS(
    _table2,"Cumulative Desp",
     IF(
        [Cumulative2]=BLANK(),BLANK(),   
    SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative2])))

vyangliumsft_1-1683103141636.png

3. Result:

vyangliumsft_2-1683103200798.png

 

Best Regards,

Liu Yang

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

abhi_kothekar
Frequent Visitor

DescriptionDesp PlanPlanned ValueDesp ActualDesp value
Assy 1Jan-23100Jan-23100
Assy 2Jan-23150Mar-23150
Assy 3Feb-23100Feb-23100
Assy 4Mar-23140Feb-23140
Assy 5Mar-23130Apr-23130
Assy 6Apr-2390TBD 
Assy 7May-2380TBD 
Assy 8Jun-23150TBD 
Assy 9Jun-23130TBD 
Assy 10Jun-23125TBD 

 

See the above table. Now I want to plot cumulative line graph for planned vs actual. 

lbendlin
Super User
Super User

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.