Forum Discussion

OneWithQuestion's avatar
OneWithQuestion
Post Prodigy
7 years ago
Solved

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 t...
Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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's avatar
edhans
Community Champion
7 years ago

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.