CALCULATE, FILTER and ALL

[anubb688]

Hi, below DAX does not display correct result. What this code should be doing is displaying "RPMD" for previous 10 periods. Each period is a week. I suspect the problem lands possibly caused by misusing of CALCULATE, FILTER and ALL. Please advise.

RPMD = DIVIDE([Revenue Calculated], [Est Exp], 0)

RPMD Last 10 =
VAR _selecteddate =
    MIN ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        [RPMD],
        FILTER (
            ALL ( 'Calendar'[Date] ),
            IF (
                'Calendar'[Date] = _selecteddate - 7,
                'Calendar'[Date],
                IF (
                    'Calendar'[Date] = _selecteddate - 14,
                    'Calendar'[Date],
                    IF (
                        'Calendar'[Date] = _selecteddate - 21,
                        'Calendar'[Date],
                        IF (
                            'Calendar'[Date] = _selecteddate - 28,
                            'Calendar'[Date],
                            IF (
                                'Calendar'[Date] = _selecteddate - 35,
                                'Calendar'[Date],
                                IF (
                                    'Calendar'[Date] = _selecteddate - 42,
                                    'Calendar'[Date],
                                    IF (
                                        'Calendar'[Date] = _selecteddate - 49,
                                        'Calendar'[Date],
                                        IF (
                                            'Calendar'[Date] = _selecteddate - 56,
                                            'Calendar'[Date],
                                            IF (
                                                'Calendar'[Date] = _selecteddate - 63,
                                                'Calendar'[Date],
                                                IF ( 'Calendar'[Date] = _selecteddate, 'Calendar'[Date], BLANK () )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )

[Greg_Deckler · Accepted solution]

@anubb688 Probably, Why not just get the current date and if you want the past 10 periods (each a week) then something like:

RPMD Last 10 = 
  VAR __SelectedDate = MIN('Calendar'[Date])
  VAR __10Prior = __SelectedDate - 70
RETURN
  CALCULATE([RPMD],FILTER('Calendar','Calendar'[Date]>=__10Prior && 'Calendar'[Date]<=__SelectedDate))

[anubb688]

It somehow shows a dot instead of line chart which i have selected

[Greg_Deckler]

Maybe:

RPMD Last 10 = 
  VAR __SelectedDate = MIN('Calendar'[Date])
  VAR __10Prior = __SelectedDate - 70
RETURN
  CALCULATE([RPMD],FILTER(ALL('Calendar'),'Calendar'[Date]>=__10Prior && 'Calendar'[Date]<=__SelectedDate))

It's really hard to know with such incomplete information.

[anubb688]

Your code worked when I tweaked my beneath formulas. Thanks. I'll mark yours as solution since I found my direction.