Filter formula using date field YYYYMM math

[gsed99]

Hello,

I am trying to create a filter using a YYYYMM date field, and subtract 1 from another date field. 

Example:

Snapshot Date=YYYYMM (201605)

SQOMonth=YYYYMM (201604)

I want to create a measure (or column) that will count # of SQO only if the SQOMonth=Snapshot Date - 1.

Meaning I want to count all SQO's with SQOMonth=201604 with the Snapshot Date=201605, or SQOMonth=201612 with Snapshot Date=201701. The report will be aggregated by SQOMonth.

Also, for the December months, I want to use the following January date, so converting 201701 to 201612, or 201601 to 201512. I was thinking a formula like Excel like: IF(Right([Snapshot Date],2="01","12",), but now sure how that can be done in DAX.

My guess is something like:

COUNT('Database'[SQOMonth])

FILTER([SQOMonth]=Snapshot Date - 1

Any help would be greatly appreciated!

[v-yuta-msft]

Hi gsed99,

Try DAX formula below:

Count Result =
CALCULATE (
    COUNT ( Database[SQOMonth] ),
    FILTER ( Database, [SQOMonth] = EDATE ( [Snapshot Date], -1 ) )
)

Regards,

Jimmy Tao

[gsed99]

Thank you, it sounds very close, but its giving me an error saying "Too few arguments were passed to the FILTER function."