cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ruthie09
Resolver II
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.
 
Please help?
1 ACCEPTED SOLUTION
Ruthie09
Resolver II
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]))
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")

View solution in original post

3 REPLIES 3
Ruthie09
Resolver II
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]))
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")
karnold
Resolver I
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 =
            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!

amitchandak
Super User
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 !!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors