topic Re: Return previous months range when multiple months selected in DAX Commands and Tips

Return previous months range when multiple months selected

Anonymous — Wed, 27 Apr 2022 21:04:23 GMT

How do I return the [_Inc Count] for the equivalent number of previous months based on the months the user selects (whether that is 1 or more)?

Details:

I have a previous month formula as follows:

_Inc Count PM = CALCULATE('Incidents'[_Inc Count], PREVIOUSMONTH('DATE Table'[Date]))

which shows me the number of tickets from the month prior to whatever month they selected (from a filter showing a list of MMM-YYYY) if they select only 1 month.

But, if the user selects more than 1 month, how do I return the [_Inc Count] for the previous equivalent number of FULL months? For example, if the user selects April-2022 and March-2022, I need to return the count for ALL of Jan and Feb (they selected 2 months so I need the two months before that range). If they select Jan, Feb and March, I need it to return Oct, Nov and Dec.

I have a previous period formula that returns the equiv number of days but in this case, I need it to return full months even if one of the months selected is only thru the 4th or whatever.

Please help?

Re: Return previous months range when multiple months selected

amitchandak — Thu, 28 Apr 2022 01:17:41 GMT

Anonymous , Try like

_Inc Count PM = CALCULATE('Incidents'[_Inc Count], dateadd('DATE Table'[Date],-1, month))

make sure DATE Table is marked as Date table in power bi

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

Re: Return previous months range when multiple months selected

karnold — Thu, 28 Apr 2022 13:31:14 GMT

Based on the details I produced an example on DAX.DO. You can experiment with it at -> https://dax.do/NwmaUnx44zszNB/ .

I assumed you might be displaying a single value.

MEASURE 'Sales'[PreviousPeriodSales] = VAR _MinDate = MIN ( 'Date'[Date] ) VAR _MaxDate = MAX ( 'Date'[Date] ) VAR _NumMonths = ( DATEDIFF ( _MinDate, _MaxDate, MONTH ) + 1 ) * -1 VAR _PrevEndDate = DATEADD ( CALCULATETABLE ( VALUES ( 'Date'[Date] ), REMOVEFILTERS ( 'Date' ), 'Date'[Date] = _MinDate ), -1, DAY ) VAR _PrevStartDate = STARTOFMONTH ( DATEADD ( CALCULATETABLE ( VALUES ( 'Date'[Date] ), REMOVEFILTERS ( 'Date' ), 'Date'[Date] = _MinDate ), _NumMonths, MONTH ) ) VAR _PrevDateRange = DATESBETWEEN ( 'Date'[Date], _PrevStartDate, _PrevEndDate ) RETURN CALCULATE ( [Sales Amount], _PrevDateRange )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Re: Return previous months range when multiple months selected

Anonymous — Thu, 28 Apr 2022 13:59:48 GMT

This is the solution I ended up going with (measures)...

To get the date range:

_Inc Count PMs =

VAR start_of_period = FIRSTDATE('DATE Table'[Date])

VAR end_of_period = LASTDATE('DATE Table'[Date])

VAR months_in_period = COUNTROWS(VALUES('DATE Table'[Year Month Name]))

VAR start_of_previous_period = FIRSTDATE(DATEADD('DATE Table'[Date],-1*months_in_period,MONTH))

VAR end_of_previous_period = LASTDATE(DATEADD('DATE Table'[Date],-1*months_in_period,MONTH))

RETURN

FORMAT(start_of_previous_period,"mm/dd/yy") & " - " & FORMAT(end_of_previous_period,"mm/dd/yy")

To get the target value, replaced the RETURN section with this:

RETURN

CALCULATE(

[_Inc Count],

DATESBETWEEN('DATE Table'[Date],start_of_previous_period,end_of_previous_period),ALL('DATE Table'))

Note: the source of one of the values used above is

'DATE Table'[Year Month Name] = FORMAT([Date],"MMM-YYYY")