Forum Discussion
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.
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.
CNH,
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzPRuVppwzju4CmB
Regards,
Lydia
10 Replies
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
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.
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?
