cancel
Showing results for
Did you mean:  Resolver II

## Return previous months range when multiple months selected

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.

1 ACCEPTED SOLUTION  Resolver II

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]))

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")
3 REPLIES 3  Resolver II

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]))

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")  Resolver I

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 =
CALCULATETABLE (
VALUES ( 'Date'[Date] ),
REMOVEFILTERS ( 'Date' ),
'Date'[Date] = _MinDate
),
-1,
DAY
)
VAR _PrevStartDate =
STARTOFMONTH (
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!  Super User

@Ruthie09 , 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

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !! Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.  