Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 follow...
Previous month
previous period
previousmonth
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
karnold
Resolver I
4 years ago

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!