Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

@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
 
Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.