Datesbetween - Line Chart Visualisation

[CNH]

 I am trying to plot on a line chart running data for 3 fiscal years. My fiscal year being 30 June and not 31 December

Data for Year 1 [1 July 2015 to 30 June 2016]

Data for Year 2 [1 July 2016 to 30 June 2017]

Data for Year 3 [1 July 2016 to 30 June 2018] This is the current year

The following is what I am hoping to achieve.

I am not certain how to achieve this.

I was thinking the following might be  of some relevance

Revenue from Start = CALCULATE(Report[Revenue], DATESBETWEEN('Dates'[Date], BLANK(), LASTDATE('Dates'[Date])))

I have had it suggested to set up a new measure "Date2". But I really am not sure how to do this. Also given that I am after a third series would I then need to set up a "Date3"

For Date2 [and Date3 if applicable], how would this/these measures be coded, ie is this logical 

Date2 = DATE(2015,07,01)
Date3 = DATE(2016,07,01)

Thanks for any help that can be offered. You will see from my question, I know what I want as an output, but have no idea how to really implement. 

[Phil_Seamark]

Hi @CNH

Is your three years of data in separate tables or columns?

Why not get them into a single column and use a date field from the same table on your axis and just drag the column you want to plot to the value field.

Power BI can happily plot years of data on a single line chart, regardless of when your years start/end

[CNH]

Hi Phil,

Revenue is sourced from:

Revenue = Report[Sales] + Report[MiscInc]

Each of these are in dataset on a transaction & date basis. So to my thinking the answer to your question is that the data is not in a separate column.

I will see whether I can push this into a new column. I am new to PowerBi/DAX and not particularly savvy as how to code – but from what I have done to date I think that seems relatively straight forward.

To the question, revenue is therefore on a transaction/daily basis so over the fiscal year 1 it accumulates from zero to the final total, it then resets at the new fiscal year 2 to zero and then accumulates to the final total, same for year 3.

I have no issue in doing an accumulation from the beginning of the first year to the end of the third year.

So my question is how do I create 3 sequences from one dataset.

If I create a new column “Revenue” what code would you suggest to enable this to be plotted as per the picture I posted previously.

Let me know if any of the above is unclear.

[Phil_Seamark]

If I understand correctly, you are after three separate lines that do not join.

The easiest way is to generate a separate column to be used in the legend that will carry the values 2016 (for all dates in your 2016 financial year), then 2017 (for all transactions in your 2017 financial year).

Does that make sense?

[CNH]

Phil, are you indicating that I create 3 columns. If so I will work on that.

Btw, I have set up the fiscal year in my date table as follows:

Fiscal Year = if(MONTH([DATE]) > 6, YEAR([DATE])+1, YEAR([DATE]))

I am not certain whether that is of any use. From the various meanderings I have done trying to plot my data it didn't seem to assist.

Thanks for your time 🙂 

[Phil_Seamark]

HI @CNH

More I mean something like this.  Just plot the Rev column on a line chart and drag Series to your legend

Date	Rev	Series
1-Jan	10	A
2-Jan	20	A
3-Jan	30	A
4-Jan	40	A
5-Jan	50	B
6-Jan	60	B
7-Jan	70	B
8-Jan	80	B
9-Jan	90	C
10-Jan	100	C
11-Jan	110	C
12-Jan	120	C
13-Jan	130	C

[CNH]

Phil, thanks for that I will work on this tomorrow and see whether I can get what I am after.

[CNH]

Hi Phil, I have had a bit of a look and I am lost.

Can I use the following as an example:

Raw data, in table "Transactions"

Date	Revenue
1/01/2016	10
2/01/2016	15
3/01/2016	20
4/01/2016	10
5/01/2016	11
6/01/2016	15
7/01/2016	20
8/01/2016	5
1/01/2017	10
2/01/2017	10
3/01/2017	14
4/01/2017	13
5/01/2017	18
6/01/2017	20
7/01/2017	25
1/01/2018	4
2/01/2018	17
3/01/2018	16
4/01/2018	14
5/01/2018	23
6/01/2018	10

What code would you use to create a measure to sum YTD. I have been using the following code to calculate YTD Revenue in 

YTD Revenue = CALCULATE(Transactions[Revenue], DATESBETWEEN('Dates'[Date], BLANK(), LASTDATE('Dates'[Date])))

I can use the following code to set the fiscal year

Fiscal Year = if(MONTH([DATE]) > 6, YEAR([DATE])+1, YEAR([DATE]))

In theory I end up with:

Date	Revenue	Ytd Revenue	Fiscal Year
1/01/2016	10	10	2016
2/01/2016	15	25	2016
3/01/2016	20	45	2016
4/01/2016	10	55	2016
5/01/2016	11	66	2016
6/01/2016	15	81	2016
7/01/2016	20	101	2016
8/01/2016	5	106	2016
1/01/2017	10	10	2017
2/01/2017	10	20	2017
3/01/2017	14	34	2017
4/01/2017	13	47	2017
5/01/2017	18	65	2017
6/01/2017	20	85	2017
7/01/2017	25	110	2017
1/01/2018	4	4	2018
2/01/2018	17	21	2018
3/01/2018	16	37	2018
4/01/2018	14	51	2018
5/01/2018	23	74	2018
6/01/2018	10	84	2018

With this data I would then be able to plot revenue as I am after, using Fiscal Year as the Legend, YTD Revenue as the values against the Date axis.

That is the theory I am trying to execute, but I do not know how.

My apologies for being so daft, but I do not know how to proceed.

Thanks if you or someone else can help me.

[v-yuezhe-msft · Accepted solution]

@CNH,

Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzPRuVppwzju4CmB

Regards,
Lydia

[CNH]

Lydia,

Could I add to this question. Having plotted actual revenue I would like to also plot 2 targets, Target 1 and Traget 2.

For year 1, Target 1 lets say is 100,000 and target 2 is 120,000. 

For year 2, Target 1 lets say is 105,000 and target 2 is 130,000. 

For year 3, Target 1 lets say is 110,000 and target 2 is 140,000. 

I would like these to plot in a ray for each fiscal year, ie year 1 Target 1 rises from zero to 100,000 over the year time line etc

Is there a measure that I could write in Power BI to provide the data to populate.

Alternatively the data for my revenue is coming from an excel spreadsheet - I could easily introduce a new worksheet "Target" which has the three year dates populated and either daily or an accumulating total for Target 1 and Target 2.

Hopefully you can assist here. I have tried to do the excel spreadsheet worksheet but it hasn't worked.

Thanking you 

[CNH]

Hi Lydia,

Thanks for that. I've used 

YTD Revenue = TOTALYTD(SUM(Transactions[Revenue]),Dates[Date],"30 06 2018")

This has in fact achieved what I was after.

Thanks to everyones input.