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.
Hi There,
I have a table with the following columns and values:-
ID | SubmissionDate | ClosureDate | OtherColumns
1 | 21-12-2016 | 23-03-2017 |
2 | 12-01-2017 | 01-04-2017 |
Any idea how to create a graph based on the count of submission and closure by different months?
Solved! Go to Solution.
Hi @Juliana,
You create a Calendar table including all the dates, please click "New Table" under modeling type the following formula on home page.
Dates=Calendar(MIN(Table[SubmissionDate]),MAX(ClosureDate))
Then create a calculated month column using the formula.
Month=MONTH(Dates[Dates])
Create a measure using the formula.
result=Calculate( count(Table[ID]), FILTER(Table, COUNTROWS(FILTER(VALUES(Dates[Dates]), Project[SubmissionDate] <= Dates[Dates] && Project[ClosureDate] >= Dates[Dates] )) > 0) )
Create a table visual, select the Table[Month] and [result] as value levels.
Best Regards,
Angelia
Hi @Juliana,
You create a Calendar table including all the dates, please click "New Table" under modeling type the following formula on home page.
Dates=Calendar(MIN(Table[SubmissionDate]),MAX(ClosureDate))
Then create a calculated month column using the formula.
Month=MONTH(Dates[Dates])
Create a measure using the formula.
result=Calculate( count(Table[ID]), FILTER(Table, COUNTROWS(FILTER(VALUES(Dates[Dates]), Project[SubmissionDate] <= Dates[Dates] && Project[ClosureDate] >= Dates[Dates] )) > 0) )
Create a table visual, select the Table[Month] and [result] as value levels.
Best Regards,
Angelia
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |