Start of Month based on Previous Date

[BlueTeam1]

Hello,

I need some help with some dax. I need to find the first of the TOTALMTD() however it needs to be based on the previous day.

Examples:

• If I provide the date 10/31/2020, it should provide the TOTALMTD() between 10/1/2020 and 10/30/2020.
• If I provide 11/1/2020, it should provide 10/1/2020 to 10/31/2020.
• If I provide 11/2/2020, it provides TOTALMTD() for 11/1/2020.

This is what I have so far:

first of month of previous day = var _prev_day = PREVIOUSDAY('Calendar'[Date])

var first_of_month = CALCULATE(STARTOFMONTH('Calendar'[Date]),YEAR('Calendar'[Date]) = YEAR([Previous Date]), MONTH('Calendar'[Date]) = MONTH([Previous Date])

)

return first_of_month

I can't seem to get the filters to work correctly. Any help is appreciated.

[AlB · Accepted solution]

Hi @BlueTeam1 

VAR end_ = 'Calendar'[Date]-1

VAR start_ = DATE(YEAR(end_), MONTH(end_), 1)

and then you can use those vars in the filter operation  You do not specify if this is to be used in a measure or a calc column. If the former, you´ll need a SELECTEDVALUE( ) in end_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers