cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Graph Last 12 Months from Latest Date

OK, I know that there are plenty of examples of this sort of problem kicking around, but I can't find one that I follow.

I have a table named MonthlySalesData. It's a very simple table containing monthly sales data for staff which looks like this:

 Date Name Value 01/01/2020 Dave 123 01/02/2020 Dave 222 01/03/2020 Dave 453 01/04/2020 Dave 323

This continues for every month for 10 staff. It doesn't reach the current date, and works in roughly a 3 month deficit (i.e. the current table only contains data up to June 2020).

All I need to do is graph data for the latest date in the Date column and the previous 11 months. So the current data would be graphed from June 2020 back to July 2019 (12 months). When it gets updated with July 2020 data, the graph would then

show July 2020 back to August 2019.

Much like my last query, this feels like a simple thing that I just can't get my head around.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: Graph Last 12 Months from Latest Date

Give something like this a try.

``````12 mo amount =
VAR _End =
CALCULATE ( MAX ( 'MonthlySalesData'[Date] ), ALL ( 'MonthlySalesData'[Date] ) )
RETURN
CALCULATE (
SUM ( 'MonthlySalesData'[Value] ),
KEEPFILTERS ( DATESINPERIOD ( 'MonthlySalesData'[Date], _End, -12, MONTH ) )
)
``````
4 REPLIES 4
Highlighted
Super User II

## Re: Graph Last 12 Months from Latest Date

Give something like this a try.

``````12 mo amount =
VAR _End =
CALCULATE ( MAX ( 'MonthlySalesData'[Date] ), ALL ( 'MonthlySalesData'[Date] ) )
RETURN
CALCULATE (
SUM ( 'MonthlySalesData'[Value] ),
KEEPFILTERS ( DATESINPERIOD ( 'MonthlySalesData'[Date], _End, -12, MONTH ) )
)
``````
Highlighted
Frequent Visitor

## Re: Graph Last 12 Months from Latest Date

@jdbuchanan71 Thanks for your incredibly fast reply.

That does calculate the previous 12 months, but I need to be able to graph each month. Using the DAX you gave me allows me to graph a single calculated figure per staff member for the previous 12 months (unless I'm using it wrong), but I need to graph each months, like this:

Highlighted
Super User II

## Re: Graph Last 12 Months from Latest Date

You are going to have to share more about your data model.  Do you have a calendar table?  Is that where you are pulling the date fields for your visual?  If you don't and you pull in the date field from the sales table to your visual does it not work?

Highlighted
Frequent Visitor

## Re: Graph Last 12 Months from Latest Date

@jdbuchanan71  It does, and I was being a massive cretin.

Thanks so much. I've had a really long break from PBI and can't get my head back into the right space.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors