Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mat42
Helper III
Helper III

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

@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
jdbuchanan71
Super User
Super User

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

@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

 

@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

 

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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