How to separate 2018 and 2019 data and shows as two lines in the line chart.

[ryan_mayu]

Hi all,

Below is my sample data. I create two measures to sum 2018 data and 2019 data.  However, I have some complex KPI, for example, rolling 3 month KPI.

Rolling 3 month calculation:

2018 Jan= null (becoz no 2017 Dec and Nov data)

2018 Feb = null (becoz no 2017 Dec data)

2018 Mar= 2018Jan+2018Feb+2018Mar

....

2019 Dec=2019 Oct+2019 Nov+2019 Dec

date	Month	amount	Order
1/1/2018	Jan	1	1
2/1/2018	Feb	2	2
3/1/2018	Mar	3	3
4/1/2018	Apr	4	4
5/1/2018	May	5	5
6/1/2018	Jun	6	6
7/1/2018	Jul	7	7
8/1/2018	Aug	8	8
9/1/2018	Sep	9	9
10/1/2018	Oct	10	10
11/1/2018	Nov	11	11
12/1/2018	Dec	12	12
1/1/2019	Jan	13	1
2/1/2019	Feb	14	2
3/1/2019	Mar	15	3
4/1/2019	Apr	16	4
5/1/2019	May	17	5
6/1/2019	Jun	18	6
7/1/2019	Jul	19	7
8/1/2019	Aug	20	8
9/1/2019	Sep	21	9
10/1/2019	Oct	22	10
11/1/2019	Nov	23	11
12/1/2019	Dec	24	12

Now I want to create a chart like below. Is it possible to do that?

Thanks a lot.

[Nolock]

Hi Ryan,

I've created 2 measures for "start of the period" and "end of the period" for your rolling 3 months. After that another measure that calculates the rolling sum. If you have the rolling sum, then you just need to create a visualisation where you use a year as legend. And one more thing: You have to sort the column Month by Month Order to be able to display Jan, Feb and so on in the expected order and not alphabetically.

Period Start = 
FIRSTDATE(DATESINPERIOD(TestTable[date]; [Period End]; -3; MONTH))

Period End = LASTDATE(TestTable[date])

Rolling Sum = 
CALCULATE(
    SUM(TestTable[amount]);
    DATESBETWEEN(TestTable[date]; [Period Start]; [Period End])
)

[ryan_mayu · Accepted solution]

@Nolock 

I worked this out. Acutally, you provided me a very useful idea which is pull the year to legend. I tried this on my end. It works. Thanks for your help on this.

[ryan_mayu]

@Nolock 

Thanks for your help. However, I think your chart is not correct. Based on my logic, we should not have value for 2018 Jan and Feb.

And for example, the value of 2018 May is 5 in your chart. That is only the summary of one month. I think the correct value should be 5+4+3= 12. 

Any thoughts on this?

Thanks in advance.

[Nolock]

Hi @ryan_mayu,

I'm sorry I've overseen that you want to remove results when the whole interval isn't there. Maybe there is a more elegant solution for that, but I used a check at the end if the length of the period is the expected length.

And I've also corrected the error you found with May 2018. It worked as a table but after that I converted in a visualisation and didn't checked the value again (the fix was to take all rows in Calculate again).

Rolling Sum = 
VAR PeriodLength = 3
VAR PeriodEnd =
    LASTDATE ( TestTable[date] )
VAR PeriodStart =
    FIRSTDATE (
        DATESINPERIOD ( TestTable[date]; PeriodEnd; - PeriodLength; MONTH )
    )
VAR IsPeriodThreeMonths =
    DATEDIFF ( PeriodStart; PeriodEnd; MONTH ) = PeriodLength - 1
VAR CalculateRollingSum =
    CALCULATE (
        SUM ( TestTable[amount] );
        ALL ( TestTable );
        DATESBETWEEN ( TestTable[date]; PeriodStart; PeriodEnd )
    )
VAR Result =
    IF ( IsPeriodThreeMonths; CalculateRollingSum; BLANK () )
RETURN
    Result