Previous month selected with interval

[jimi123]

Hi Could someone help me with the below

I have a slice filter where I have my Month-Year field from my calendar table

I need to have a measure in which I should capture a column field related to the previous month.

For example, in the case above it should return Apr 23 - Total Session Value (here the it's correct)

THE PROBLEM: is that if I unselected Apr 23 from the slicer filter I expect that the value returned should be from Mar 23, but it keeps returning the value from Apr 23....I need that the returned value "Total Sessions last Month" should be 70881

[Anonymous · Accepted solution]

Hi @jimi123 ,

I created a sample file for your reference.

Sample data:

There's no relationship between the main table and the calendar table.

Create a measure for the visual-level filters.

Measure = 
var _select=SELECTCOLUMNS('Calendar',"1",'Calendar'[MonthYear])
var _date1=
MAXX(
    FILTER(ALL('Calendar'),'Calendar'[MonthYear] in _select),[Date])
var _datecolumn=
SELECTCOLUMNS(
    FILTER(ALL('Calendar'),'Calendar'[MonthYear] in _select&&YEAR(MAX('Table'[Date]))=YEAR(_date1)&&MONTH(MAX('Table'[Date]))<>MONTH(_date1)),"1",[Date])
return
IF(ISFILTERED('Calendar'[MonthYear]),IF(
    MAX('Table'[Date]) in _datecolumn,1,0),1)

Put the measure into the Filters and set up show items when the value is 1.

Here's the result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[some_bih]

Hi @jimi123 I understand you just need data for previous month? If yes your measure should be

CALCULATE(SUM(<your column name for summing>), PREVIOUSMONTH('Calendar'[Date]))

https://learn.microsoft.com/en-us/dax/previousmonth-function-dax 

I hope this help