Count months in slicer period

[oitp]

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? 🙂

[v-jiascu-msft]

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

[themistoklis · Accepted solution]

@oitp

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

[oitp]

@themistoklisthank you! I guess I should make a relationship between the date column in the Calendar table and date in my table?

[themistoklis]

@oitp

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