Line and column chart with 2 accumulated value lines, one per year

[Anonymous]

Hello,

I'm using two tables:  F_AMOUNTS and F_KPIS
F_AMOUNTS  has following columns: MONTH, GROUP and AMOUNT
For every month and for every group the table countains an amount. A month is refered to by the last day of the month. 


F_KPIS  has following columns: YEAR, GROUP and KPI
For every year and for every group the table countains a KPI amount. A year is refered to by the last day of the year.

I added following 4 columns to F_AMOUNTS:

Month Abreviation = FORMAT(F_AMOUNTS[MONTH];"MMM")            Month Abriviation is sorted by Month Order 
Month Order = MONTH(F_AMOUNTS[MONTH])

Year = YEAR(F_AMOUNTS[MONTH])

KPI Month = DIVIDE( LOOKUPVALUE(F_KPIS[KPI]; F_KPIS[YEAR]; DATE(YEAR(F_AMOUNTS[MONTH]);12;31); F_KPIS[GROUP];  F_AMOUNTS[GROUP]); 12)

For every month I calculate the acumulative amount separately per group and per year, using this measure:

Cumulated Amount = CALCULATE(  SUM(F_AMOUNT[AMOUNT]);  FILTER(  ALLSELECTED(F_AMOUNT);  F_AMOUNT[MONTH] <= MAX(F_AMOUNT[MONTH]) &&  F_AMOUNT[GROUP] = MAX(F_AMOUNT[GROUP]) &&  YEAR(F_AMOUNT[MONTH]) = YEAR(MAX(F_AMOUNT[MONTH]))  )  )

I also calculate for every month the acumulative KPI separately per group and per year, using this measure:

Cumulated KPI =  CALCULATE(  SUM(F_AMOUNTS[KPI MONTH]);  FILTER(  ALLSELECTED(F_AMOUNTS);  F_AMOUNTS[MONTH] <= MAX(F_AMOUNTS[MONTH]) &&  F_AMOUNTS[GROUP] = MAX(F_AMOUNTS[GROUP]) &&  YEAR(F_AMOUNTS[MONTH]) = YEAR(MAX(F_AMOUNTS[MONTH]))  )  )

MONTH	GROUP	Cumulated Amount	Cumulated KPI
31/01/2019	A	240	167
28/02/2019	A	446	333
31/03/2019	A	574	500
30/04/2019	A	661	667
31/05/2019	A	777	833
30/06/2019	A	848	1000
31/07/2019	A	1051	1167
31/08/2019	A	1205	1333
30/09/2019	A	1442	1500
31/10/2019	A	1681	1667
30/11/2019	A	1924	1833
31/12/2019	A	2144	2000
31/01/2020	A	250	200
29/02/2020	A	463	400
31/03/2020	A	706	600
30/04/2020	A	881	800
31/05/2020	A	1167	1000
30/06/2020	A	1269	1200
31/07/2020	A	1493	1400
31/08/2020	A	1767	1600
30/09/2020	A	1897	1800
31/10/2020	A	2074	2000
30/11/2020	A	2137	2200
31/12/2020	A	2271	2400
31/01/2019	B	136	150
28/02/2019	B	263	300
31/03/2019	B	376	450
30/04/2019	B	557	600
31/05/2019	B	656	750
30/06/2019	B	940	900
31/07/2019	B	1188	1050
31/08/2019	B	1419	1200
30/09/2019	B	1578	1350
31/10/2019	B	1665	1500
30/11/2019	B	1864	1650
31/12/2019	B	2026	1800
31/01/2020	B	138	175
29/02/2020	B	250	350
31/03/2020	B	358	525
30/04/2020	B	546	700
31/05/2020	B	800	875
30/06/2020	B	871	1050
31/07/2020	B	1114	1225
31/08/2020	B	1268	1400
30/09/2020	B	1355	1575
31/10/2020	B	1541	1750
30/11/2020	B	1649	1925
31/12/2020	B	1815	2100

I'm using a Line and Clustered Column Chart with Month Abreviation on shared axis, column series Year, column values Cumulated Amount and line values Cumulated KPI.

If I add a slicer on GROUP en select A then I get this result:

But I would like to have two lines: one for the Cumulated KPI over 2019 and one for he Cumulated KPI over 2020 as I also have the Cumulated Amount for 2019 and for 2020 in the columns

Something like this:

Does anybody have an idea how to do this?

Thanks,

R.W.

[Anonymous]

Hi @v-yingjl 

Thanks for this solution.
But I also had to rewrite my other measures in the same style:

2019 Cumulated Amount = 
CALCULATE (
    SUM ( 'F_AMOUNTS'[Amount] ),
    FILTER (
        ALL ( F_AMOUNTS ),
        MONTH ( 'F_AMOUNTS'[MONTH] ) <= MONTH ( MAX ( 'F_AMOUNTS'[MONTH] ) )
            && YEAR ( 'F_AMOUNTS'[MONTH] ) = 2019
            && 'F_AMOUNTS'[GROUP] IN DISTINCT ( 'F_AMOUNTS'[GROUP] )
    )
)

2020 Cumulated Amount = 
CALCULATE (
    SUM ( 'F_AMOUNTS'[Amount] ),
    FILTER (
        ALL ( F_AMOUNTS ),
        MONTH ( 'F_AMOUNTS'[MONTH] ) <= MONTH ( MAX ( 'F_AMOUNTS'[MONTH] ) )
            && YEAR ( 'F_AMOUNTS'[MONTH] ) = 2020
            && 'F_AMOUNTS'[GROUP] IN DISTINCT ( 'F_AMOUNTS'[GROUP] )
    )
)

And I had to drop the Legend value Year.
So I have two measures for the columns en two measures for the lines.

R.W.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

The 'Column series' field is just applied for the columns, not for lines. So you may need to create measures for each year to calculate:

2019 Cumulated KPI = 
CALCULATE (
    SUM ( 'F_AMOUNTS'[KPI Month] ),
    FILTER (
        ALL ( F_AMOUNTS ),
        MONTH ( 'F_AMOUNTS'[MONTH] ) <= MONTH ( MAX ( 'F_AMOUNTS'[MONTH] ) )
            && YEAR ( 'F_AMOUNTS'[MONTH] ) = 2019
            && 'F_AMOUNTS'[GROUP] IN DISTINCT ( 'F_AMOUNTS'[GROUP] )
    )
)

2020 Cumulated KPI = 
CALCULATE (
    SUM ( 'F_AMOUNTS'[KPI Month] ),
    FILTER (
        ALL ( F_AMOUNTS ),
        MONTH ( 'F_AMOUNTS'[MONTH] ) <= MONTH ( MAX ( 'F_AMOUNTS'[MONTH] ) )
            && YEAR ( 'F_AMOUNTS'[MONTH] ) = 2020
            && 'F_AMOUNTS'[GROUP] IN DISTINCT ( 'F_AMOUNTS'[GROUP] )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.