cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

## How to write PREVIOUSMONTH manually without builtin function?

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.

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
2 REPLIES 2
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting