Forum Discussion

DAXRichArd's avatar
DAXRichArd
Icon for Resolver I rankResolver I
3 years ago

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

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.

 

 

Need Help

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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 ()
        )
    )