cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
oitp Frequent Visitor
Frequent Visitor

Count months in slicer period

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? Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
themistoklis New Contributor
New Contributor

Re: Count months in slicer period

@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

4 REPLIES 4
themistoklis New Contributor
New Contributor

Re: Count months in slicer period

@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 Frequent Visitor
Frequent Visitor

Re: Count months in slicer period

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

themistoklis New Contributor
New Contributor

Re: Count months in slicer period

@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 

Community Support Team
Community Support Team

Re: Count months in slicer period

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.

Count-months-in-slicer-period

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.