Forum Discussion
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))
Thank you all, I'll work through all of them.
4 Replies
- krishnakanth240Super User
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'))- DickenPost Prodigy
Thank you all, I'll work through all of them.
- mizan2390Super User
It may work.
Count Previous Months :=
CALCULATE(
DISTINCTCOUNT( 'Calendar'[Month Number] ),
FILTER(
ALL( 'Calendar' ),
'Calendar'[Date] <= MAX( 'Calendar'[Date] )
),
KEEPFILTERS( 'Sales' )
) - FreemanZSuper User
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))