Forum Discussion

astarkey's avatar
astarkey
Frequent Visitor
8 years ago
Solved

count anything OLDER than previous month

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

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      astarkey
      Frequent Visitor
      8 years ago

      Top Man!!

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