Forum Discussion

CNH's avatar
CNH
Helper I
8 years ago
Solved

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 ...
Need Help
CNH's avatar
CNH
Helper I
8 years ago

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

CNH's avatar
CNH
Helper I
8 years ago

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.

 

 

 

  • CNH's avatar
    CNH
    Helper I
    8 years ago

    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.

  • CNH's avatar
    CNH
    Helper I
    8 years ago

    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