Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
OneWithQuestion
Post Prodigy
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
TomMartens
Super User
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

View solution in original post

2 REPLIES 2
TomMartens
Super User
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
edhans
Super User
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 I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors