How to write PREVIOUSMONTH manually without builtin function?

[OneWithQuestion]

I need the functionality of PREVIOUSMONTH, but I am not using it against months.  Instead I am looking at custom intervals (usually, but not always every 2 weeks).

There is a column in the date table that has an interval value entered for each date in the date tables.  There are two intervals per month.

I'm having issues doing previous interval calculations as I can't just use CALCULATE(SUM(someTotal),PreviousMonth(DateTable[IntervalValue]).

Instead I just need to go back one value, I was hoping to learn how PREVIOUSMONTH works inside DAX so I could recreate it with a CALCULATE statement and then modify it to go back 1 interval.  

Thank you

EDIT: I wanted to avoid having to add an interval count column to the table where I just take the current interval count and -1.  I was hoping there was a way to do this with MAX(interval)<current interval or something but... I can not get anything like that to work.

[TomMartens · Accepted solution]

Hey,

I guess this might be something you are looking for:

Value of Previous Interval = 
var maxRunningIndex = MAX('Dimension Date'[Interval])
return
CALCULATE(
    <the expression>
    ,FILTER(
        ALL('Dimension Date')
        ,'Dimension Date'[Interval] = maxRunningIndex - 1
    )
)

The assumption behind this is: the date table is related (on the one side) with the fact table (on the many side).

Regards,
Tom

[edhans]

I'd recommend reading Matt's article on this. You will probably want to create the custom interval column in Power Query before bringing it in to the data model, then use the DAX logic @MattAllington  discusses over that column. He also has an entire chapter in his Super Charge Power BI book that goes over custom date logic. Definitely worth the price of the book for just that chapter if you don't have a standard fiscal year.