YTD line chart

[mbegg]

Hi,  

I have monthly data with 30 June year end back to July 2014 (so 3 fiscal years worth). I want a YTD line chart with each fiscal year a separate colour line. 

I have created a quick measure, and produced a graph with Axis as Month and Legend as Year per the attached. 

The calculation is working correctly - the lowest value in each year is in July (period 1) and the highest value is in June (period 12).

However, both the axis and legend are based on the calendar year rather than the 30 june fiscal year end. I want july to be the first month on the axis and june to be the last. Likewise, there should only be 3 colours on the legend (2015, 16 & 17), not 4. 

Picture 1I have tried creating a separate 'fiscal' date field (called Fiscal_period_sold) based on adding 6 months to the calendar month (so 1/Jul/2014 becomes 1/Jan/15 and 1/Jun/15 becomes 1/Dec/15). This fixes the legend, but the axis remains January through to December rather than July through to June. See picture 2. 

 Picture 2

[GilbertQ · Accepted solution]

Hi @mbegg

I would suggest creating a YTD measure where you use a Date Table also. This will ensure that you can then get the right values for your measures.

YTD = TOTALYTD(sum('TableName'[ColumnName]),'Date'[Calendar Date],ALL('Date'),"06/30")

And here is a blog post around why you need a date table and how to create one.

Do you need a Date Dimension

[ashishrj]

@mbegg Hi, you can think of creating a new calculated column using DAX (switch/if) which states that July = 1 ....June = 12 so that later you can sort your month column using this new calculated column and probably you can see your axis properly sorted & in requried format. Hope this helps!

[mbegg]

Hi @ashishrj, thank you for the suggestion, I have tried that without success. 

1. Do I add the new calculated column (call it Fiscal_Month) as another axis?
2. If so, when you said "so that later you can sort your month column using this new calculated column" - is this within the data window or on the actual chart? 

If I do (1) above, I am not given the option to sort the chart by Fiscal_Month. See Picture 3. 

I know other BI software allows users to set a default sort of a particular column but I can't see that functionality in Power BI anywhere.

My only other thought is how can I change the format of Month on the horizontal axis in the below picture to appear as 01 to 12?

Picture 2

[v-ljerr-msft]

Hi @mbegg,

I know other BI software allows users to set a default sort of a particular column but I can't see that functionality in Power BI anywhere.

---

The Sort by Column option is under Modeling tab in Power BI Desktop. For more details about how to use this option, you can refer to this similar thread. 

Regards

[mbegg]

Hi @v-ljerr-msft,

Thank you for the guidance, that is helpful. I can replicate for monthly data, but unfortunately it doesn't solve my YTD problem (yet). I am not sure but I think it is because the YTD calculation requires the Power BI-provided data heirarchy on the horizontal axis rather than another field such as your Month_Name. 

I can sort the date field (Period_sold in my photos) by my Fiscal_Month but this doesn't seem to apply to the YTD chart. 

See below photos. 

Picture 4

Picture 5

[v-ljerr-msft]

Hi @mbegg,

Could you share a sample pbix file which can reproduce the issue, so that we can further assistant on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[mbegg]

Thank you @v-ljerr-msft, here it is in dropbox 

Dropbox_YTD line chart

[v-ljerr-msft · Accepted solution]

Hi @mbegg,

As @GilbertQ mentioned above, an individual Calendar table is needed in this case. You can use CALENDARAUTO function to create it, and then create a relationship between your fact table and this Date table with the date column.

Date = CALENDARAUTO()

And the formula below is for the new YTD measure using Date[Date] column.

Value_YTD_Fis = 
IF(
	ISFILTERED('Date'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy"),
	TOTALYTD(SUM('Sheet1'[Value]), 'Date'[Date],"30/6")
)

Here is the modified pbix file for your reference. 

Regards

[GilbertQ · Accepted solution]

Hi @mbegg

I would suggest creating a YTD measure where you use a Date Table also. This will ensure that you can then get the right values for your measures.

YTD = TOTALYTD(sum('TableName'[ColumnName]),'Date'[Calendar Date],ALL('Date'),"06/30")

And here is a blog post around why you need a date table and how to create one.

Do you need a Date Dimension