Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
7 months ago
Solved

Count previous months

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

  • 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'))

  • 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))

  • Dicken's avatar
    Dicken
    7 months ago

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

4 Replies

  • 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's avatar
      Dicken
      Post Prodigy

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

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

  • 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))