Count number of months in fact table based on slicer selection

[agagnon]

Hello,

I am trying to count the number of months in my fact table based on the selection of a slicer.  I have tried three different measures that work to varying degree.

The problem really comes if I select months out of sequence.  Selecting January and February shows the number 2 for two months but selecting January and March show the number 3 even though it should show two.

Ultimately this will be used in a simple formula =(Actual/#ofmonths)*12

The cards in the screenshots below are in order of the versions listed below (V1 is the first card, V2 is the second card...)

Anythoughts on how to achieve this functionality?

Month's of Actual = MONTH(LASTDATE(Actual[Date])) 

Month's of Actual v2 = 
DATEDIFF(MIN(Actual[Date]),MAX(Actual[Date]),MONTH
)+1

Month's of Actual v3 = count(Dates[MonthName])

[jdbuchanan71 · Accepted solution]

Hello @agagnon ,

I am assuming you have a Date table hooked to your Actual table.  You can use that and count the MonthName column.

Month Count =
CALCULATE (
    COUNTROWS ( VALUES ( 'Date'[MonthName] ) ),
    ALLSELECTED ( 'Date'[MonthName] )
)

Although, this will give a wrong answer if the user selects multiple years since it is only counting distinct month names.  A better answer would be to count the Month Year column (Jan-2018, Jan-2019 etc).

Month Count =
CALCULATE (
    COUNTROWS ( VALUES ( 'Date'[Month Year] ) ),
    ALLSELECTED ( 'Date'[Month Year] )
)

It will still work if they select only a single year but will also work if they select multiple years. 

[agagnon]

@jdbuchanan71 Thank you so much for your quick reply!

I had a feeling I was going about this the wrong way.  The date table is hooked up and your formula works perfectly.

My brain is so used to thinking in terms of Excel, that getting used to DAX, context, and thinking about things in Tables and Table functions is a work in progress.

Much appreciated.