Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

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 ...
Need Help
  • Shravan133's avatar
    Shravan133
    2 years ago

    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))

Shravan133's avatar
Shravan133
Super User
2 years ago

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's avatar
Oros
Post Prodigy
2 years ago

Hi Shravan133,

 

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

 

 

 

  • Shravan133's avatar
    Shravan133
    Super User
    2 years ago

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

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      Hi Shravan133 ,

       

      Thank you very much.  Your solution works very well!