Same period previous N months

[Anonymous]

I want to calculate total sales for previous N months for the same period according to the slicer. Eg if in slicer I select range from 1-10-21 to 3-10-21 the my column chart should display total sales for previous N months within this range.

I wrote following Dax measure for the same but not getting desired result:

same period = CALCULATE(SUM(financials[ Sales]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -'Select N'[N Value],MONTH),FILTER(financials,AND(financials[Date]>=MIN('Date'[Date]),financials[Date]<=MAX('Date'[Date]))))

Any suggestions are apprecited.

[ankur1991]

Is there any hardcode version in which you can see the sales amount of same period last month.

for example, if i have data of 1 dec to 14 dec then it should display data of 1 nov to 14 nov.

[Icey]

Hi @Anonymous ,

You can also create a measure like so:

same period = 
VAR SelectedDates_ =
    VALUES ( 'Date'[Date] )
VAR SelectedDays_ =
    VALUES ( 'Date'[Day] )
RETURN
    CALCULATE (
        SUM ( financials[Sales] ),
        DATESINPERIOD (
            'Date'[Date],
            MAX ( 'Date'[Date] ),
            - 'Select N'[Select N Value] - 1,
            MONTH
        ),
        'Date'[Day] IN SelectedDays_,
        NOT ( 'Date'[Date] IN SelectedDates_ )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulOlding]

Hi @Anonymous 

You can use the DATEADD function to move the period back a set number of months

same period = 
VAR _SelectedN = SELECTEDVALUE('Select N'[N Value])
VAR _Result = 
CALCULATE(
    SUM(financials[Sales]),
    DATEADD('Date'[Date], -_SelectedN, MONTH)
)
RETURN
    _Result

[Anonymous]

@PaulOlding  if in slicer I select range from 1-10-21 to 3-10-21 the my column chart should display total sales for previous N months within this range. 

I have already achieved result for total sales for previour N months

[PaulOlding]

Sorry, I don't understand.

If the user selects 1-10-21 to 3-10-21 and previous 3 months, what dates should we be looking at?

*Also, is 1-10-21 in d-m-yy or m-d-yy?

[Anonymous]

If date range for a particular month is selected than for every selected N previous months, total sales pertaining to only that date range should be displayed. The format is d-m-yy.

[PaulOlding]

Does that mean these dates?

1-9-2021

2-9-2021

3-9-2021

1-8-2021

2-8-2021

3-8-2021

1-7-2021

2-7-2021

3-7-2021

[Anonymous]

yes

[PaulOlding]

OK.  How many previous months can the user select?

I'm wondering if it's better to have a pattern like:

IF(N Value >= 1, CALCULATE(..., DATEADD(Date, -1, MONTH) 

+ IF(N Value >= 2, CALCULATE(..., DATEADD(Date, -2, MONTH) 

+ IF(N Value >= 3, CALCULATE(..., DATEADD(Date, -3, MONTH) 

etc

... or if it's better to construct a variable with the required dates manually.

The advantage of DATEADD is it handles the complexity of months having different numbers of days. 

For example, what happens if the user selects 24-2-2021 to 3-3-2021?  February only has 28 days but moved back one month you should probably include 29, 20 & 31 January.

What if they select 24-2-2021 to 28-2-2021?  Does that mean we should go from 24 Jan to 31 Jan?  or 24 Jan to 28 Jan?

[Anonymous]

I wrote something like this.

same period = CALCULATE(SUM(financials[ Sales]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -'Select N'[N Value],MONTH),DATESBETWEEN('Date'[Date],MIN('Date'[Date]),MAX('Date'[Date])))

Is it correct?

[PaulOlding]

Hi @Anonymous 

No, i don't think so.

For a start you'll get a syntax error from this 'Select N'[N Value].  I suspect you'd want it to be SELECTEDVALUE('Select N'[N Value]).  However, even then you won;t get the right answer.

We can break the measure formula down to see why:

same period = 
CALCULATE(
    SUM(financials[Sales]),
    DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -SELECTEDVALUE('Select N'[N Value]),MONTH),
    DATESBETWEEN('Date'[Date],MIN('Date'[Date]),MAX('Date'[Date]))
)

 We're using CALCULATe so we're going to alter the filter context.  The arguments we pass for the second parameter onwards are tables.  Let's see what they'll contain for the example selection: Date = 1-Oct-2021 to 3-Oct-2021

N Value = 3

DATESINPERIOD

https://dax.guide/datesinperiod/

This will return all the dates going back 3 months from the start date of 3-Oct-2021.  So all dates between 4-Jul-2021 and 3-Oct-2021

DATESBETWEEN

https://dax.guide/datesbetween/

This will return all the dates between 1-Oct-2021 and 3-Oct-2021.  

So, the net effect of those 2 filters in the calculate function is the dates that are in both results.  That's 1-Oct-2021 to 3-Oct-2021.

Here's a version of your measure that allows you to go back 4 periods.  If you need more you can add more IF statements in the same format

same period 2 = 
VAR _SelectedN = SELECTEDVALUE('Select N'[N Value])
VAR _Result = 
IF(_SelectedN >= 1, CALCULATE( SUM(financials[Sales]), DATEADD('Date'[Date], -1, MONTH) ))
    + IF(_SelectedN >= 2, CALCULATE( SUM(financials[Sales]), DATEADD('Date'[Date], -2, MONTH) ))
    + IF(_SelectedN >= 3, CALCULATE( SUM(financials[Sales]), DATEADD('Date'[Date], -3, MONTH) ))
    + IF(_SelectedN >= 4, CALCULATE( SUM(financials[Sales]), DATEADD('Date'[Date], -4, MONTH) ))
RETURN 
    _Result