cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CNH Regular Visitor
Regular Visitor

Datesbetween - Line Chart Visualisation

 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.

3 year chart.jpg

 

 

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Datesbetween - Line Chart Visualisation

@CNH,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Phil_Seamark Super Contributor
Super Contributor

Re: Datesbetween - Line Chart Visualisation

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

CNH Regular Visitor
Regular Visitor

Re: Datesbetween - Line Chart Visualisation

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 Super Contributor
Super Contributor

Re: Datesbetween - Line Chart Visualisation

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

CNH Regular Visitor
Regular Visitor

Re: Datesbetween - Line Chart Visualisation

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 Smiley Happy 

Phil_Seamark Super Contributor
Super Contributor

Re: Datesbetween - Line Chart Visualisation

HI @CNH

 

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

 

DateRevSeries
1-Jan10A
2-Jan20A
3-Jan30A
4-Jan40A
5-Jan50B
6-Jan60B
7-Jan70B
8-Jan80B
9-Jan90C
10-Jan100C
11-Jan110C
12-Jan120C
13-Jan130C

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

CNH Regular Visitor
Regular Visitor

Re: Datesbetween - Line Chart Visualisation

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

CNH Regular Visitor
Regular Visitor

Re: Datesbetween - Line Chart Visualisation

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"

DateRevenue
1/01/201610
2/01/201615
3/01/201620
4/01/201610
5/01/201611
6/01/201615
7/01/201620
8/01/20165
1/01/201710
2/01/201710
3/01/201714
4/01/201713
5/01/201718
6/01/201720
7/01/201725
1/01/20184
2/01/201817
3/01/201816
4/01/201814
5/01/201823
6/01/201810

 

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:

 

 

DateRevenueYtd RevenueFiscal Year
1/01/201610102016
2/01/201615252016
3/01/201620452016
4/01/201610552016
5/01/201611662016
6/01/201615812016
7/01/2016201012016
8/01/201651062016
1/01/201710102017
2/01/201710202017
3/01/201714342017
4/01/201713472017
5/01/201718652017
6/01/201720852017
7/01/2017251102017
1/01/2018442018
2/01/201817212018
3/01/201816372018
4/01/201814512018
5/01/201823742018
6/01/201810842018

 

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.

 

 

 

Moderator v-yuezhe-msft
Moderator

Re: Datesbetween - Line Chart Visualisation

@CNH,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CNH Regular Visitor
Regular Visitor

Re: Datesbetween - Line Chart Visualisation

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.