Forum Discussion
Last X MONTHS based on selected MONTH
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.
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))Hi,
I have solved a similar question in the attached file.
Hope this helps.
9 Replies
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))try using the month number instead of month from the date table in the measure.
Hi,
I have solved a similar question in the attached file.
Hope this helps.
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.
It works fine in the file that i shared with you. Please study it carefully and then apply those formulas.
