DAX for Displaying last month's data. But change the display according to the selected filter.

[may-natta]

Please help me, 

Because I use Dax: CALCULATE([Direct Cost],PREVIOUSMONTH(DimDate[Date])) to return last month's data. But when selecting a filter The data is displayed retrospectively from the filter.

Ex.

  

I would like to know if there is a way to start by showing last month's data. but changes the display according to the selected filter. and if multiple filters are selected The information will be sum.

thank you for answers. 😁

[rubayatyasmin]

Hi, @may-natta 

From your explanation, it seems like you want to adjust your DAX formula to display the data of the last month, based on the month selected in the filter. To ensure that your calculation reflects the month selected in the filter, you can use the MAX function to get the last date from the filtered context, and then adjust your PREVIOUSMONTH function accordingly.

example

CALCULATE(
[Direct Cost],
PREVIOUSMONTH(
EOMONTH(MAX(DimDate[Date]), 0)
)
)

[may-natta]

I tried fixing as you suggested. But found an error as shown in the picture.

I'm looking for a way to get the graph to start showing at the last month. But if selecting a specific month filter The data will change depending on the filter I select. And if I select the filter for more than 1 month, then the data for each month that I select is counted together.

[rubayatyasmin]

The PREVIOUSMONTH function expects a column reference as its argument, not a scalar value. The MAX(DimDate[Date]) provides a scalar value (the maximum date in the current filter context).

One approach to handle this is to use the LASTDATE function instead of MAX, which provides a reference to a single-row table containing the latest date, thus making it compatible with PREVIOUSMONTH. Here is how you could adjust your formula:

CALCULATE(
[Direct Cost],
FILTER(
ALL(DimDate),
DimDate[Date] > EOMONTH(LASTDATE(DimDate[Date]), -2) &&
DimDate[Date] <= EOMONTH(LASTDATE(DimDate[Date]), -1)
)
)

This formula will consider only the dates that fall in the month preceding the latest date in your selected filter context. So, if you select multiple months, it will sum up the "Direct Cost" for the last month in your selection.

Remember, if there are other filters active on your DimDate table, they might interfere with this formula, because the ALL function is removing only the filter from the Date column, not from the entire table. If necessary, you could replace ALL(DimDate) with ALLSELECTED(DimDate) to respect other filters.

[may-natta]

I fixed it according to the instructions. Data is displayed as 0 😭

Is it possible that if I don't select a filter? Last month display data But if I select the 1 month filter, then the data is displayed as selected. And if I select more than 1 month filter, let the data be counted together. according to the sample picture.

Do not select any filters.

Select filter 1 month

Select filter 2 months

[rubayatyasmin]

@may-natta can you provide demo pbix file or data file? I would like to try it.