topic Re: How to show previous month when selected month slicer. in DAX Commands and Tips

How to show previous month when selected month slicer.

Anonymous — Fri, 10 May 2024 10:55:23 GMT

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

)

Re: How to show previous month when selected month slicer.

johnbasha33 — Fri, 10 May 2024 09:48:21 GMT

Anonymous

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 !!

Re: How to show previous month when selected month slicer.

Anonymous — Fri, 10 May 2024 10:54:18 GMT

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

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

Re: How to show previous month when selected month slicer.

xifeng_L — Fri, 10 May 2024 16:53:56 GMT

Hi Anonymous ,

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~

Re: How to show previous month when selected month slicer.

Anonymous — Sat, 11 May 2024 12:37:28 GMT

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