Measure based on date-filter: Need to show only past 12 months based on selection

[Ras_Ile]

Hi Community,

I have been struggling now for a very long time and I hope that you can help me figure out this one.

I am attempting to write a measure that will show values only for the past twelve months based on the month that I select from a slicer.

Let's say I have a fact table with two columns: [Sales] and [Year.Month]. [Year.Month] is related to a date table. 
I have a dashboard with a bar chart that shows monthly sales and a date slicer. By selecting, for example, [Year.Month] = Feb.24 on the slicer, I want the bar chart to show sales between Feb.24 and Mar.23.

How could I possibly achieve this?  
I expect this is quite tricky to achieve. Possibly with some kind of calculated column in my date table based on selectedvalue (?)

Help greatly appreciated,
BR Rasmus 

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.  Please review the formulas in the file and apply them to your data.

Hope this helps.

[v-huijiey-msft]

Hi @Ras_Ile ,

Please try:

Measure1 =
VAR MonthStart = MIN('DateTable'[Date])
VAR MonthEnd = MAX('DateTable'[Date])
RETURN
CALCULATE(
     SUM('FactTable'[Sales]),
     DATESBETWEEN(
         'DateTable'[Date],
         DATEADD(MonthStart, -11, MONTH),
         MonthEnd
     )
)

This measure is expected to show values for the last 12 months.

Please try:

Create a calculated column that calculates the end date for each start date (for example, February 24 to March 23).

End Date = EDATE([Year.Month], 1) - 1

Create a measure.

Measure2 =
VAR StartDate = SELECTEDVALUE('Date'[Year.Month])
VAR EndDate = CALCULATE(MAX('Date'[End Date]), 'Date'[Year.Month] = StartDate)
RETURN
IF(
     AND(
         MAX('FactTable'[Year.Month]) >= StartDate,
         MAX('FactTable'[Year.Month]) <= EndDate
     ),
     1,
     0
)

Use Measure2 as a Filter for the bar chart, set to display items when value = 1.

The bar chart is expected to show sales between February 24th and March 23rd.

I would be very grateful if you could provide me with sample data, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Ras_Ile]

Dear Yang @v-huijiey-msft ,

Please find attached sample data. Sensitive data has been removed. 

https://drive.google.com/file/d/11maHsr59yj0jlNjZQYoXbvVVPPNhaumq/view?usp=sharing 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.  I was facing some errors when i opened the file so i had to delete some tables.  Your question has been solved though.

Hope this helps.

[Ras_Ile]

Thank you very much @Ashish_Mathur  !! 

[Ashish_Mathur]

You are welcome.

[Ras_Ile]

Hi! Thanks so much for this. 

Have an issue though in measure 1. 
The RETURN formula is not able to recognize variable "MonthStart" in the DATEADD formula.
Not sure why - I am using the exact same logic, and my date column is formatted as "Date".

Thoughts?