Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |