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

     

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