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 c...
Need Help
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 ()
        )
    )