Count previous months

[Dicken]

If i have  a  pivot or table  with Year / Month  ; 
I want to count the previous number of months  present so not just the month number  
as some do not have any data so are not present 

so if i had 
Jan sales 1

March sales 2 
May sales 3    

so the best i have so far is ; 

Count Previous Months:=VAR Myear =
    IF( HASONEVALUE( 'Calendar'[Year]),VALUES( 'Calendar'[Year]))
VAR Mmonth = 
    IF (HASONEVALUE( 'Calendar'[Month Number]), VALUES('Calendar'[Month Number]))
RETURN
CALCULATE (
    DISTINCTCOUNT ( 'Calendar'[Month Number]),   
      FILTER(ALL('Calendar'), 'Calendar'[Month Number]<= Mmonth && 'Calendar'[Year] = Myear))

so this counts the months regardless of whether there are 'sales' present 

Richard

[krishnakanth240 · Accepted solution]

Hi @Dicken 

Can you please try this measure

Count Previous Months=
CALCULATE(DISTINCTCOUNT('Calendar'[Month Number]),FILTER(ALL('Calendar'),
'Calendar'[Date]<=MAX('Calendar'[Date])),
KEEPFILTERS('Sales'))

[FreemanZ · Accepted solution]

hi @Dicken ,

try like:

Count Previous Months:=

VAR Myear =

    IF( 

     HASONEVALUE( 'Calendar'[Year]),

     VALUES( 'Calendar'[Year])

)

VAR Mmonth = 

    IF (HASONEVALUE( 'Calendar'[Month Number]), VALUES('Calendar'[Month Number]))

RETURN

CALCULATE (

    COUNTROWS ( 'sales'),   

      FILTER(ALL('Calendar'), 'Calendar'[Month Number]<= Mmonth && 'Calendar'[Year] = Myear))

[FreemanZ · Accepted solution]

hi @Dicken ,

try like:

Count Previous Months:=

VAR Myear =

    IF( 

     HASONEVALUE( 'Calendar'[Year]),

     VALUES( 'Calendar'[Year])

)

VAR Mmonth = 

    IF (HASONEVALUE( 'Calendar'[Month Number]), VALUES('Calendar'[Month Number]))

RETURN

CALCULATE (

    COUNTROWS ( 'sales'),   

      FILTER(ALL('Calendar'), 'Calendar'[Month Number]<= Mmonth && 'Calendar'[Year] = Myear))

[mizan2390]

It may work.
Count Previous Months :=
CALCULATE(
DISTINCTCOUNT( 'Calendar'[Month Number] ),
FILTER(
ALL( 'Calendar' ),
'Calendar'[Date] <= MAX( 'Calendar'[Date] )
),
KEEPFILTERS( 'Sales' )
)

[krishnakanth240 · Accepted solution]

Hi @Dicken 

Can you please try this measure

Count Previous Months=
CALCULATE(DISTINCTCOUNT('Calendar'[Month Number]),FILTER(ALL('Calendar'),
'Calendar'[Date]<=MAX('Calendar'[Date])),
KEEPFILTERS('Sales'))

[Dicken · Accepted solution]

Thank you all, I'll work through all of them.