topic Re: Same period previous N months in DAX Commands and Tips

Same period previous N months

Anonymous — Thu, 07 Oct 2021 10:39:05 GMT

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.

Re: Same period previous N months

PaulOlding — Thu, 07 Oct 2021 11:44:42 GMT

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

Re: Same period previous N months

Anonymous — Thu, 07 Oct 2021 12:02:20 GMT

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

Re: Same period previous N months

PaulOlding — Thu, 07 Oct 2021 12:11:00 GMT

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?

Re: Same period previous N months

Anonymous — Thu, 07 Oct 2021 12:18:13 GMT

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.

Re: Same period previous N months

PaulOlding — Thu, 07 Oct 2021 12:35:06 GMT

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

Re: Same period previous N months

Anonymous — Thu, 07 Oct 2021 13:09:15 GMT

yes

Re: Same period previous N months

PaulOlding — Thu, 07 Oct 2021 13:17:22 GMT

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?

Re: Same period previous N months

Anonymous — Fri, 08 Oct 2021 10:20:24 GMT

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?

Re: Same period previous N months

PaulOlding — Sat, 09 Oct 2021 17:46:45 GMT

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

Re: Same period previous N months

Icey — Fri, 29 Oct 2021 07:01:48 GMT

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.

Re: Same period previous N months

ankur1991 — Wed, 07 Feb 2024 10:31:21 GMT

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.