topic Re: Previous month selected with interval in DAX Commands and Tips

Previous month selected with interval

jimi123 — Thu, 15 Jun 2023 06:57:36 GMT

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

Re: Previous month selected with interval

some_bih — Thu, 15 Jun 2023 09:52:07 GMT

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

Re: Previous month selected with interval

Anonymous — Thu, 22 Jun 2023 07:12:05 GMT

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.