Time series comparative analysis, sum incorrect previous years, multiple slicer filters select v all

[DAXRichArd]

Hello,

I am sure this has been address.

I've hacked away at this forum but could not find the response.

Current Situaiton

• Commercial aviation data.
• Time intervals; data is monthly data.
• Most current year is 11 months of the calendar year.
• Objective: time series comparative analysis.
• Time intelligence in use:
  • Filter calendar table using CALCULATE
  • DATEADD
  • DATESBETWEEN

    PREVIOUSMONTH
  • TOTALYTD
  • SAMEPERIODLASTYEAR

Output results to a table (see pics attached).

Output is a monthly time series with aggregations for year to date.

Complicating Factor

•  For previous years, I include in my measure 

IF (

            ISBLANK ( [summed numerical column] ),

            BLANK (),

• That way only data for the months of the most current year are displayed.
  • e.g. if this year I only have 11 months of data (Jan - Nov) previous years will also only dispay 11 months of data.

Problem

• If my slicer is set to all months, previous years show 11 months but total 12 months.
• I want previous years to follow the current year and total for the months shown.
• e.g. if my current year has 11 months, all time series totals (this year, last year, 2 years ago, year 2019....) to only the 11 months being displayed.

Thx in advance for all your help.

[sturlaws]

Hi,

this is one way of solving your issue, although sligthly verbose:

MeasureCurrentYear cumulative =
VAR _year =
    CALCULATE ( SELECTEDVALUE ( Dates[Year] ) )
VAR _month =
    CALCULATE ( SELECTEDVALUE ( Dates[MonthNum] ) )
VAR _maxMonthCurrentYear =
    CALCULATE (
        MAX ( 'Table'[month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Year] = _year )
    )
RETURN
    IF (
        _month <= _maxMonthCurrentYear
            && HASONEVALUE ( Dates[Month] ),
        CALCULATE (
            SUM ( 'Table'[NumberOfPassengers] ),
            FILTER ( ALL ( Dates ), Dates[Year] = _year && Dates[MonthNum] <= _month )
        ),
        IF (
            NOT ( HASONEVALUE ( Dates[Month] ) ),
            SUMX (
                CALCULATETABLE (
                    'Table',
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Year] = _year
                            && 'Table'[month] <= _maxMonthCurrentYear
                    )
                ),
                'Table'[NumberOfPassengers]
            ),
            BLANK ()
        )
    )

MeasureCurrentYear-1 cumulative =
VAR _year =
    CALCULATE ( SELECTEDVALUE ( Dates[Year] ) )
VAR _month =
    CALCULATE ( SELECTEDVALUE ( Dates[MonthNum] ) )
VAR _maxMonthCurrentYear =
    CALCULATE (
        MAX ( 'Table'[month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Year] = _year )
    )
RETURN
    IF (
        _month <= _maxMonthCurrentYear
            && HASONEVALUE ( Dates[Month] ),
        CALCULATE (
            SUM ( 'Table'[NumberOfPassengers] ),
            FILTER ( ALL ( Dates ), Dates[Year] = _year - 1 && Dates[MonthNum] <= _month )
        ),
        IF (
            NOT ( HASONEVALUE ( Dates[Month] ) ),
            SUMX (
                CALCULATETABLE (
                    'Table',
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Year] = _year - 1
                            && 'Table'[month] <= _maxMonthCurrentYear
                    )
                ),
                'Table'[NumberOfPassengers]
            ),
            BLANK ()
        )
    )