Measure for Prior Month to Date

[RobX]

I'm trying to create a measure that calculates the prior month to date based on the dates selected in a filter pane.  If I select 1/1/2023 through 1/9/2023, I want the prior month results to be for 12/1/2022 through 12/9/2022.

I found another thread that suggested this DAX measure:

SpendMTD = CALCULATE(

    SUM(Spend[Spend]),

    DATEADD(Calendar[Date], -1, MONTH)

)

But that seems to provide the sum for the full previous month, not just through the 9th.

[Anonymous · Accepted solution]

Hi @RobX  ,

I create a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

Previous month spends of selected date range = 
VAR _mindate =  MIN ( 'Calendar'[Date] )
VAR _maxate = MAX ( 'Calendar'[Date] )
VAR _pminedate = EOMONTH ( _mindate, -1 )
VAR _pmaxedate = EOMONTH ( _maxate, -1 )
VAR _minday = DAY ( _mindate )
VAR _maxday = DAY ( _maxate )
VAR _pmindate =
    IF (
        _minday > DAY ( _pminedate ),
        _pminedate,
        DATE ( YEAR ( _pminedate ), MONTH ( _pminedate ), _minday )
    )
VAR _pmaxdate =
    IF (
        _maxday > DAY ( _pmaxedate ),
        _pmaxedate,
        DATE ( YEAR ( _pmaxedate ), MONTH ( _pmaxedate ), _maxday )
    )
RETURN
    CALCULATE (
        SUM ( 'Spend'[Spend] ),
        FILTER ( 'Spend', 'Spend'[Date] >= _pmindate && 'Spend'[Date] <= _pmaxdate )
    )

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[RobX]

Thank you.  This solved the issue I was having.

[Greg_Deckler]

@RobX Try:

SpendMTD = 
  VAR __CMBegin = MIN('Calendar'[Date])
  VAR __CMEnd = MAX('Calendar'[Date])
  VAR __PMBegin = EDATE(__CMBegin, -1)
  VAR __PMEnd = EDATE(__CMEnd, -1)
  VAR __Result = CALCULATE(SUM(Spend[Spend]), Calendar[Date]>=__PMBegin && Calendar[Date]<= __PMEnd)
RETURN
  __Result


or

SpendMTD = 
  VAR __CMBegin = MIN('Calendar'[Date])
  VAR __CMEnd = MAX('Calendar'[Date])
  VAR __PMBegin = EDATE(__CMBegin, -1)
  VAR __PMEnd = EDATE(__CMEnd, -1)
  VAR __Result = SUMX(FILTER(Spend, [Date]>=__PMBegin && [Date]<= __PMEnd),[Spend])
RETURN
  __Result