Running Total for non-calendar year line chart

[Anonymous]

Hi,

I'm struggling creating a line chart showing running total over non-calendar (Gas) years, that would correctly perserve the order of the months.

Desired features:

• A Gas year is 1 Oct - 30 Sept. Hence the line chart should be showing Oct first and Sept last. This means that Oct 2020 and January 2021 are for the sake of this chart both 2021 (as shown in the data below), and cumulative total should start on Oct 1st.
• The chart should take user filtes into account, hence it needs to be calculated using a meassure applying existing filters

Data (note the Category column for filter testing purposes)

For a calendar year, the solution is relatively simple, using TOTALYTD function

Q_YTD = TOTALYTD(Sum('Table'[Quantity]), 'Table'[Date].[Date], ALLSELECTED('Table'))

Axis: Date.Month

Legend: Date.Year

Values: Q_YTD

So the desired line chart would have Oct - Sept months on the x-axis, calculating running total across those months using GasYear (instead of Calendar year). The quantities should stay allocated to months as they are. Keep in mind that running total is starting in October (not Jan).

I was able to calculate the numbers correctly (using last argument into TOTALYTD function as "30/09"), but I'm really struggling to then plot the results on a line chart with the months in desired order (Oct - Sept) and correct Gas Years in legend.

Gas years for illustration

Attaching PBIX

Any help greatly appreciated,

Stefan

[v-deddai1-msft · Accepted solution]

Hi @Anonymous  ,

If you want to calculate YTD according to your fiscal year, I suggest you use the following dax code:
 

Q_YTD =

CALCULATE (

    SUM ( 'Table'[Quantity] ),

    FILTER (

        ALL ( 'Table' ),

        'Table'[Date] <= MAX ( 'Table'[Date] )

            && 'Table'[GasYear] = MAX ( 'Table'[GasYear] )

    )

)

You can use a calendar table to sort the months on the X-axis by your fiscal year. Please follow the steps below:

Create a calendar table:

Date = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))

Create month and fiscal month columns in the calendar table:

Month = FORMAT('Date'[Date].[Month],"MMM")

FYMonthNum =

VAR FYStartMonth = 10

RETURN

    IF (

        MONTH ( 'Date'[Date] ) >= FYStartMonth,

        MONTH ( 'Date'[Date] ) - ( FYStartMonth - 1 ),

        12 + MONTH ( 'Date'[Date] ) - ( FYStartMonth - 1 )

    )

Sort the month column by FYMonthNum column:

Associate the calendar table with the original table:

Use Month as Axis :

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW803EfvBQ1Muao92mfHsfABCxwe68n0sQSPeEDNx0cD6A?e=cp1cOE

Best Regards,

Dedmon Dai

[Anonymous]

HI @v-deddai1-msft ,

This is StefanZ - your solution works, thank you so much for you effert. 

And apologies for not marking your answer as as correct, I'm unable to log in with my original accout, I don't know why. The Power Bi Community sign in protocols are really complicated. 

Kind Regards

Stefan