Hi,
I have seen previous post regarding 12 months rolling total. My query is slightly different. I know I can use Time intelligence function to calculate 12M rolling totals.
My issue is that the dates are in different table (Related table with EOM Column) but when I use calendar dates in the DAX it doesn’t calculate 12 months rolling total.
I need to use Calendar dates as a slicer for the page so I can’t change.
Can someone please help me in.
Attached is the PBIX. 12M rolling total is showing 15496 but that is actually for whole period from 01/07/19-31/08/2020 not12 months.
Thank you
MannyTEST1_crossfilter.pbix (102.3 KB)
Hello @mannymalhotra,
Thank You for posting your query onto the Forum.
Well in your file, there was no direct relationship between the Dates table and the dates column in the Drivers All table. So firstly, I created an inactive relationship between the two. Since you’ve already created undirected the relationship between the Dates Table and End Of Month Table and from there End Of Month to Drivers All table.
Now, the only possibility left is to create inactive relationship between the Dates Table and dates column of the Drivers All table and then use the USERELATIONSHIP function in the formula to activate the relationship virtually.
Below is the formula alongwith the screenshot of the result provided for the reference -
12M Rolling Total - Harsh =
CALCULATE( SUM( 'Drivers All'[Value] ) ,
DATESINPERIOD(
'Calendar'[Date] ,
MAX( 'Calendar'[Date] ) ,
-12 ,
MONTH ) ,
USERELATIONSHIP( 'Calendar'[Date] , 'Drivers All'[Date] ) )
I’m also attaching the working of the PBIX file for the reference. And providing a link of the video of USERELATIONSHIP function.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Thanks and Warm Regards,
Harsh
TEST1_crossfilter - Harsh.pbix (94.4 KB)
https://info.enterprisedna.co/dax-function-guide/userelationship/
2 Likes
I believe if you remove the end of month table from your data model and connect calendar directly to drivers via date that the 12 month rolling total will now calculate correctly if you remove the crossfilter statement from your measure:
I hope this is helpful. Full solution file attached below
- Brian
eDNA forum – Test CrossFilter Solution.pbix (108.3 KB)
1 Like
See attached,
All we needed to do was add another Date table to your model and establish a relationship to your [Drivers All] table. I have a number of projects which have 3 or more date tables in them. Sometimes we look at work date, bill date, or pay date. Having a table established for each metric gives you easy flexibility when working with your DAX formulas.
I have uploaded a PBIX file with the formula working properly.
Let me know if you have any questions,
Malcolm
TEST1_crossfilter.pbix (133.2 KB)
2 Likes
Hi Brian,
Thanks for the reply. No I need that relations as Drivers table as EOM dates and when lets say my visual which is running from calendar date table doesn’t have EOM it should still show values of that relevant month.
For example Driver table has value of 100 employee in depot A and date is 31/08/2020. So If I am scrolling the date in my visual and lets say I put 14/08/2020 I still want Depot A to show 100 employee. Hence I have that relationship and measure working from that relationship.
Thank you again for your reply.
Cheers
Manny
Hi Harsh,
Thank you for your help.
I am just having one issue with that. I tried before what you have suggested and the issue I have with that is and I am not able figure that out.
Lets say from your pbix. Depot A and using your 12M measure.
Now the dates in Driver table is EOM and when I change the dates in the visual lets say I dont put 31/08/2020 instead I use 30/08/2020 so what the measure is doing 12 months from July which I dont want. 12 months value when 31/08/2020 is in the slicer visual is 1190 and when changed to 30/08/2020 it moves to 1192 which is Aug19-Jul20.
Is there anyway to resolve it?
Thank you
Manny
I have done this and it works, just wanted to share with you 
I am learning a lot from this great platform
12M Rolling Total =
VAR lastdate_all =
CALCULATE(
MAX( ‘ Calendar ’ [End of Month] ),
ALL( ‘ Calendar ’ )
)
VAR lastdate_current = MAX( ‘ Calendar ’ [End of Month] )
VAR getlastdate =
IF(
lastdate_all = lastdate_current,
lastdate_all,
lastdate_current
)
RETURN
CALCULATE(
SUM( ‘ Drivers All ’ [Value] ),
DATESINPERIOD(
‘ Calendar ’ [Date],
getlastdate,
-12,
MONTH
),
USERELATIONSHIP( ‘ Calendar ’ [Date], ‘ Drivers All ’ [Date] )
)
Thanks Harsh and All for your help, much appreciated
1 Like
That’s terrific – glad you got to the desired outcome. This is a great example of collaborative community problem-solving, and one of the things I enjoy most about this forum and community.
Kudos to @MalcolmJ for jumping in with a proposed solution on his first forum post. I found there’s absolutely no more effective way of leveling up my own skills than working solutions on the forum. Hope to see a lot more of your solutions in the future.
Nice outcome all around - thanks to all.
– Brian