Last X MONTHS based on selected MONTH

[Oros]

Hello,

I have a date table and a sales table.  What would be the correct measure if I would like to show the last X months based on the selected month.  For example, if I need to show the last 6 months, and I select December 2023, then the table should show the sales starting from July 2023. Thanks.

[Shravan133 · Accepted solution]

Hi,

Try this:

SalesLastXMonths =
VAR SelectedMonth = MAX('Date'[Month]) -- Get the selected month
VAR SelectedYear = MAX('Date'[Year]) -- Get the selected year
VAR StartDate =
EDATE(DATE(SelectedYear, SelectedMonth, 1),
-6 )-- Adjust this value to show the last X months (e.g., -6 for 6 months)

RETURN
CALCULATE([TotalSales],FILTER(ALL('DateTable'),'DateTable'[Date] >= StartDate &&'DateTable'[Date] <= DATE(SelectedYear, SelectedMonth, 1) - 1))

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[Oros]

Hi @Ashish_Mathur ,

Thank you for your reply.  Maybe I am missing something or a filter.  How do I show the last six months, including the month that is selected. It is NOT showing in this selection.

[Ashish_Mathur]

It works fine in the file that i shared with you.  Please study it carefully and then apply those formulas.

[Oros]

Hi @Ashish_Mathur ,

It works!  Thanks a lot!

[Ashish_Mathur]

You are welcome.

[Shravan133 · Accepted solution]

Hi,

Try this:

SalesLastXMonths =
VAR SelectedMonth = MAX('Date'[Month]) -- Get the selected month
VAR SelectedYear = MAX('Date'[Year]) -- Get the selected year
VAR StartDate =
EDATE(DATE(SelectedYear, SelectedMonth, 1),
-6 )-- Adjust this value to show the last X months (e.g., -6 for 6 months)

RETURN
CALCULATE([TotalSales],FILTER(ALL('DateTable'),'DateTable'[Date] >= StartDate &&'DateTable'[Date] <= DATE(SelectedYear, SelectedMonth, 1) - 1))

[Oros]

Hi @Shravan133,

Thank you for your reply.  I am having the following error.  Any idea?  Thanks again.

[Shravan133]

try using the month number instead of month from the date table in the measure.

[Oros]

Hi @Shravan133 ,

Thank you very much.  Your solution works very well!