count anything OLDER than previous month

[astarkey]

any help here will be most appreciated!!

I'm trying to create a measure that will count records older than the previous month..

So if today anything older than the 1st of March (feb and back) I can do this in a card with filters quite easily, by setting a date, something I then have to alter at the start of each month to keep the number correct...

However I don't want to do that and I also need the measure to help create a percentage figure.

I've tried.....

older than previous month = CALCULATE(DISTINCTCOUNT(AllJobView[JTJobNo]), < PREVIOUSMONTH('AllJobView'[jtpromisedate]))

but it doesnt like the "<"

When I remove it, I get an error saying that the specified column is not a date type (this is probably a totally seperate issue... It is a date field)

but no luck

i'm basically trying to count jobs that are feb and older, then next month march and older etc.... but want to do this without manual filter setting each month.

cheers

[OwenAuger · Accepted solution]

Hi there,

You can get away without creating a Date table but you might want to consider adding one later on.

Here are a couple of options just using your AllJobView table:

older than previous month =
CALCULATE (
    DISTINCTCOUNT ( AllJobView[JTJobNo] ),
    AllJobView[jtpromisedate] <= EOMONTH ( TODAY (), -2 )
)

Count before previous month = 
CALCULATE ( 
    DISTINCTCOUNT ( AllJobView[JTJobNo] ),
    DATESBETWEEN ( AllJobView[jtpromisedate], BLANK(), EOMONTH( TODAY(), -2 ) )
)

Regards,

Owen

[astarkey]

Top Man!!

This simple looking fix is going to help me across a number of my reports! Cheers