How to show previous month when selected month slicer.

[Pony]

I have cumulative measure like this. It cumulative from Oct-Sep in the year

I run in to problem when I want select Jan. I want to show value from Out-Jan cumulative on graph

The result should be like picture below

This is my cumulative measure.

ExpenseYTD =

CALCULATE(

   SUM(Expenses[Expense]),

    FILTER(

        ALL('Date'),

        'Date'[Date] <= MAX('Date'[Date]) &&

        'Date'[FiscalYear] = MAX('Date'[FiscalYear]) &&

        IF(SUM('Expenses'[Expense]) = BLANK(), 0, 1) = 1

    )

)

[xifeng_L · Accepted solution]

Hi @Pony ,

If your slicer uses the same month as the line chart, pls first change the slicer's month to another month field of other table to avoid the Auto-Exist mechanism.

You can copy the date table and name it Date2, without establishing any relationships with other tables. Then, Use the month of Date2 as the field for the slicer.

Additionally, based on the line chart you provided, the month field should have a sorting reference field, otherwise the months on the line chart should be out of order.

So, we can modify the measure as follows:

ExpenseYTD-Modify =
VAR SelectedSlicerMonthIndex = MAX('Date2'[Month Index])
VAR CurLineChartMonthIndex = MAX('Date'[Month Index])
RETURN
IF(CurLineChartMonthIndex <= SelectedSlicerMonthIndex,
    CALCULATE(
       SUM(Expenses[Expense]),
        FILTER(
            ALL('Date'),
            'Date'[Date] <= MAX('Date'[Date]) &&
            'Date'[FiscalYear] = MAX('Date'[FiscalYear]) &&
            IF(SUM('Expenses'[Expense]) = BLANK(), 0, 1) = 1
        )
    )
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Pony]

Thank you so much!!! Your solution is working.

[johnbasha33]

@Pony 

To adjust your cumulative measure to show the value for January correctly, you need to modify the filter condition to include the specific month. You can achieve this by adding an additional condition that checks if the month of the date is less than or equal to the selected month. Here's how you can modify your measure:

ExpenseYTD =
CALCULATE(
SUM(Expenses[Expense]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date]) &&
'Date'[FiscalYear] = MAX('Date'[FiscalYear]) &&
MONTH('Date'[Date]) = MONTH(MAX('Date'[Date]))
)
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Pony]

Sorry If I make you misunderstand. My measure done the cumulative correctly.

The result should like pictuer below if I select only Jan.