cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

DateNameValue
01/01/2020Dave123
01/02/2020Dave222
01/03/2020Dave453
01/04/2020Dave323

 

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
Super User II

@Mat42 

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

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

@Mat42 

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

View solution in original post

Highlighted

@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:

 

Mat42_1-1600962552452.png

 

Highlighted

@Mat42 

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?

jdbuchanan71_0-1600963840330.png

 

 

Highlighted

@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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors