Forum Discussion

abhi_kothekar's avatar
abhi_kothekar
Frequent Visitor
3 years ago
Solved

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 m...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  abhi_kothekar ,

     

    Here are the steps you can follow:

    1. Create calculated table.

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

    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])))

    3. Result:

     

    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