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

Slicer - How can I count selected months?

Hi Guys,

 

how can I or is it possible to count how many months I have selected in a Date-Slicer? I want to divide a value with the count of monts, e.g. earnings / selected months ... All variants do not show me the correct number of months - only distinctcount brings 12 months Smiley Wink

 

Thanks in advance

10 REPLIES 10
vega Member
Member

Re: Slicer - How can I count selected months?

Count = 
IF(
    ISFILTERED(Months[Month]),
    COUNT(Months[Month]),
    0
 )

This will count if the months column is selected directly. If you want to account for the months column being filtered indirectly, use ISCROSSEDFILTERED instead of ISFILTERED.

posterme Frequent Visitor
Frequent Visitor

Re: Slicer - How can I count selected months?

Hi Vega,

 

thank you - unfortunately, the value is always "0" instead of, in this example, "5":

 

2018-02-13 19_26_02-PIMPowerBI_PO_CRMOnline - Power BI Desktop.png

 

BR

vega Member
Member

Re: Slicer - How can I count selected months?

Can you post your Date table? How is the slicer being created? Like I said earlier, if the months are not being filtered directly, then you need to use ISCROSSFILTERED.

posterme Frequent Visitor
Frequent Visitor

Re: Slicer - How can I count selected months?

Hi,

 

I have tested both ISFILTERED and ISCROSSFILTERED - below you'll find the screenshot from the table field:

 

2018-02-13 19_41_41-PIMPowerBI_PO_CRMOnline - Power BI Desktop.png

 

Slicer (Timeline) is configured only with the "Date" field:

 

2018-02-13 19_43_44-PIMPowerBI_PO_CRMOnline - Power BI Desktop.png

 

Again - many thanks for your support Smiley Happy

 

vega Member
Member

Re: Slicer - How can I count selected months?

Does your date table have a months column?

posterme Frequent Visitor
Frequent Visitor

Re: Slicer - How can I count selected months?

Hi, its only a column defined as date field...

 

UPDATE:

 

And as I written in my first message,

 

with COUNT('TABLE'[Date].[Month]) the Value is 1096,

with DISTINCTCOUNT('TABLE'[Date].[Month]) it is 12 ...

 

But what is "1096" ? Smiley Happy

vega Member
Member

Re: Slicer - How can I count selected months?

I believe 1096 is the number of dates in your date column

YellOwBoyz New Member
New Member

Re: Slicer - How can I count selected months?

There is certainly more optimize way to do it.

But for example you can duplicate your date column convert it into a type text, and concatenate the year and the month.

And then do a DISTINCTCOUNT of your new column.

 

Example of M query to get your year and month

Text.Range([Date], 6,4) & Text.Range([Date], 3, 2)

cylix Visitor
Visitor

Re: Slicer - How can I count selected months?

i too would like to know how to do this