Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 ) )
)
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:
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 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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |