Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey guys, I'm looking to take a return a certain value to my line graph.
I have two tables, which are currently related. Table A and Table B.
This is my current equation:
DIVIDE(CALCULATE(SUM(TableA['TotalSpent]),TableA['Months] in {"July 2018","August 2018","September 2018}),
SUM(TableB['TotalAvailable'],0))
On my Line graph, I'm going to have Month and Year as the x-axis and the percentage of that dax measure above shown as the y -axis. I want to use that function above, but in my filter, take the current month and the next 2 months and divide by the Total Available for every Month and Year in the X axis. Is there a way to change the filter so I dont have type in the specific date range I want to select?
For example: In the X-axis for date, if the point is July 2018, it would be the Sum of Total Spent in July 2018, August 2018, and September 2018 (table A) / Sum Total Available for July 2018 (table B)
The next month (August 2018) would then be the Sum of Total Spent in August 2018, September 2018, October 2018 (table A) / Sum Total Available for August 2018(table B)
Thanks!
Solved! Go to Solution.
Hi @Anonymous
You may create a calendar table and get the rank of the YearMonth column.Then create measures as below.Attached sample file for your reference.
Sum of Total Spent = CALCULATE ( SUM ( TableA[Spent] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Rank] >= MAX ( 'Calendar'[Rank] ) && 'Calendar'[Rank] <= MAX ( 'Calendar'[Rank] ) + 2 ) )
Regards,
Hi @Anonymous
You may create a calendar table and get the rank of the YearMonth column.Then create measures as below.Attached sample file for your reference.
Sum of Total Spent = CALCULATE ( SUM ( TableA[Spent] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Rank] >= MAX ( 'Calendar'[Rank] ) && 'Calendar'[Rank] <= MAX ( 'Calendar'[Rank] ) + 2 ) )
Regards,
Is it possible to Inner join by ID's when rolling? Meaning that I want to Roll 3 months where Table B ID's are in Table A ID's, and by month,year.
Hi @Anonymous
Yes,it's possible.You may link tableA and tableB with ID.For further,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.
Regards,
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |