Forum Discussion

mbegg's avatar
mbegg
Advocate 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 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

 

 

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

8 Replies

  • ashishrj's avatar
    ashishrj
    Power Participant
    9 years ago

    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's avatar
      mbegg
      Advocate 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
        Microsoft 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