cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @Ras_Ile ,

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

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

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!

Dear Yang @v-huijiey-msft ,

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Thank you very much @Ashish_Mathur  !!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors