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