Calculate prior period measure without dates

[FearDerBeard]

My data has a date surrogate key, but doesn't really follow a clean calendar.  For example, a period may have  key of 201750 and a prior period key of 201650.  So I don't think I can use time intelligence functions to calculate what the period fact is. 

I could easily calculate the prior period by subtracting -100 from the current period, but I'm not sure how to pass that back to DAX.

This is what I tried but didn't work:

Measure = calculate(distinctcount(mytable[customer_id]), filter(mytable,mytable[period] = mytable[period]-100))

Could someone please help?

[Sean · Accepted solution]

@FearDerBeard

Try something like this...

Measure =
CALCULATE (
    DISTINCTCOUNT ( mytable[customer_id] ),
    FILTER ( ALL ( mytable ), mytable[period] = MAX ( mytable[period] ) - 100 )
)

[FearDerBeard · Accepted solution]

So thanks to Mark Russo.  I found the proper solution.

http://www.sqlbi.com/articles/custom-year-over-year-calculation-in-dax/

[Sean · Accepted solution]

@FearDerBeard

Try something like this...

Measure =
CALCULATE (
    DISTINCTCOUNT ( mytable[customer_id] ),
    FILTER ( ALL ( mytable ), mytable[period] = MAX ( mytable[period] ) - 100 )
)

[FearDerBeard]

I found my error:

Measure =
CALCULATE (
    DISTINCTCOUNT ( mytable[customer_uid] ),
    FILTER ( ALL ( mytable[period] ), mytable[period] = MAX( mytable[period]) -100)
)

This worked.  Now, why is the grand total summing incorrectly.  It shows the prior period and not the sum for all prior periods