Forum Discussion

mbegg's avatar
mbegg
Icon for Advocate II rankAdvocate II
9 years ago
Solved

YTD line chart

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 meas...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    Regards

mbegg's avatar
mbegg
Icon for Advocate II rankAdvocate II
9 years ago

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's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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. :smileyhappy:

 

 

Regards

  • mbegg's avatar
    mbegg
    Icon for Advocate II rankAdvocate II
    9 years ago

    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's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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. :smileyhappy:

       

      Regards