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, I have an table with tasks, time spent on the task and date. I use a slicer to only show tasks/time within a date period.
Now I want to calculate the number of months within the period chosen by the slicer. I have tried using DATEDIFF(MIN(datecolumn);MAX(datecolumn);MONTH) but it wont show the correct value.
I think the problem is that my date column doesnt show ALL dates from the slicer period since I dont have a task each day.
Slicer period: 2018-01-01 to 2018-11-30
Table data based on slicer period
Task time spent date
Task nr1 1 hour 2018-01-20
Task nr2 2 hours 2018-03-01
Task nr3 1 hour 2018-09-20
Can I count the months based on the period chosen with the slicer? 🙂
Solved! Go to Solution.
Good practice is to have a customer calendar in a report.
Custom calendars have continuous days based on the period you specify.
Then you can just simply count the months and based on your filter you will get the result you want.
A sample calendar is available on this link
Hi @oitp,
If you don't have a date table, how did you select the 2018-11-30 based on your data here? If you have a model like it in the image, the formula should work.
Best Regards,
Dale
@themistoklisthank you! I guess I should make a relationship between the date column in the Calendar table and date in my table?
That's correct. To get it from the workspace that i shared with you, simply copy it and paste it on yours.
Also make sure the cross filter direction to set to Single
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |