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

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

      • GilbertQ's avatar
        GilbertQ
        Icon for Super User rankSuper User
        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