Dax - Previous Month

[Anonymous]

I need some help changing the formula below. At present the formula calculates a cumulative total up to the current date.

Cumulative E&A Wells (Today):=

var __currentMaxDate = MAX('Calendar'[Date])
var __currentMinDate = MIN('Calendar'[Date])
var __today = TODAY()
var __cumulativevalue =
CALCULATE(
COUNTROWS('FM Full Export'),
DATESYTD('Calendar'[Date]),
'Calendar'[Date] <= __currentMaxDate,
ALLSELECTED('Calendar'[Date])
)
var __shouldDisplay =
--check if the current period is fully contained in the future
NOT(__currentMinDate >= __today)
return
IF(__shouldDisplay, __cumulativevalue)

I need the formula to calculate a cumulative total only up to the previous month but when I change the formula to the below and try to make a pivot table I get an error telling me the "A table of multiple values was supplied where a single value was expected". I understand why this error is appearing but cannot figure out how to request the previous month cumulative total.

Cumulative E&A Wells (Previous Month):=

var __currentMaxDate = MAX('Calendar'[Date])
var __currentMinDate = MIN('Calendar'[Date])
var __previousmonth = PREVIOUSMONTH('Calendar'[Date])
var __cumulativevalue =
CALCULATE(
COUNTROWS('FM Full Export'),
DATESYTD('Calendar'[Date]),
'Calendar'[Date] <= __currentMaxDate,
ALLSELECTED('Calendar'[Date])
)
var __shouldDisplay =
--check if the current period is fully contained in the future
NOT(__currentMinDate >= __previousmonth)
return
IF(__shouldDisplay, __cumulativevalue)

Many thanks in advance.

[Anonymous · Accepted solution]

Cumulative E&A Wells (Prev Month) :=
VAR __currentMinDate = MIN ( 'Calendar'[Date] )
VAR __currentMaxDate = MAX ( 'Calendar'[Date] )
-- PREVIOUSMONTH is a function that returns a table
-- not a single value, hence you cannot use it in
-- a comparison like:
-- 'Calendar'[Date] <= __prevMonthEndDate
VAR __prevMonthEndDate = EOMONTH( __currentMaxDate, -1 )
VAR __today = TODAY ()
VAR __cumulativevalue =
    CALCULATE (
        COUNTROWS ( 'FM Full Export' ),
        DATESYTD ( 'Calendar'[Date] ),
        'Calendar'[Date] <= __prevMonthEndDate,
        ALLSELECTED ( 'Calendar'[Date] )
    )
VAR __shouldDisplay =
	NOT ( __currentMinDate >= __today )
RETURN
    IF ( __shouldDisplay, __cumulativevalue )

Best

Darek

[Anonymous]

@Anonymous - great stuff, thanks very much for the help.