Graphing a cumulative curve against a custom sorted fiscal date in Power BI

Question

I'm having some trouble trying to graph a cumulative sum measure vs. a fiscal calendar date. There are no issues using the popular cumulative sum pattern against a standard date calendar, but once the fiscal date adjustment is introduced PowerBI begins to interpret the dates incorrectly.

This is the cumulative sum DAX expression I'm using:

Cumulative = 
CALCULATE(
    SUM(Table[Value]),
    FILTER(
        ALL('Date Lookup'[FiscalDate]),
        'Date Lookup'[FiscalDate] <= MAX('Date Lookup'[FiscalDate]
    )
)

The problem arises from the fact that my fiscal year begins October 1st and ends September 30th. PowerBI can't seem to handle the custom sorting I'm using to ensure the year increments every October 1st.

Here's a snip of my Date Lookup table:

Date       | FiscalDate | YearMonth
-----------|------------|----------
08/01/2010 | 11/01/2010 | 201008
09/01/2010 | 12/01/2010 | 201009
10/01/2010 | 01/01/2011 | 201010
11/01/2010 | 02/01/2011 | 201011
12/01/2010 | 03/01/2011 | 201012
01/01/2011 | 04/01/2011 | 201101

Note: The day component has been excluded from the date table as I am only concerned with the month and year components. Both the Date and FiscalDate columns are Date/Time data types, and YearMonth is of type Whole Number.

The YearMonth column is being used to sort the FiscalDate column which allows October 2017 to come directly after September 2016, although this sorting seems to break the DAX expression.

I've looked into multiple DAX functions that support fiscal dates (such as TOTALYTD, CALENDARAUTO) to no avail.

Any help is appreciated, and feel free to let me know if any other information is needed.

Leonard Leonard · Accepted Answer · 2017-01-31T19:08:16

Where I've implemented fiscal years, I've had a "fiscal year" column in my date table, and put in an additional clause into my measure:

Cumulative = 
CALCULATE(
SUM(Table[Value]),
FILTER(
    ALL('Date Lookup'),
    'Date Lookup'[FiscalDate] <= MAX('Date Lookup'[FiscalDate]
    && 'Date Lookup'[FiscalYear] = MAX('Date Lookup'[FiscalYear]
)
)

Note that I also updated the ALL clause to apply to the whole table, rather than just the FiscalDate column.

The extra clause says to only do a cumulative sum for the current fiscal year. Each fiscal year resets the cumulative total. In essence, a Fiscal YTD measure.

Graphing a cumulative curve against a custom sorted fiscal date in Power BI

1 Answers