Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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 |
Phil, thanks for that I will work on this tomorrow and see whether I can get what I am after.
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.
@CNH,
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzPRuVppwzju4CmB
Regards,
Lydia
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |