Total current month if prior month value = 0

[KBedor]

Help!  I've been tried more suggestions from this community than I can count, including Greg Deckler's patterns, and still can't seem to get the correct total.  

My fact table has project budgets by Month and Year.  I've created measures for current and prior months, which work as expected regardless off the month selected in the slicer.  

Now the business wants to see current month values where Prior Month = 0 so I wrote this measure:

NoPriorAmount = VAR prior = [PriorMoEACRev]

VAR currmon = [CurrEACRev]

RETURN IF(prior=0,[CurrEACRev],0)

Of couse the total row value is 0.

The measure will need to be sliced by a location and business unit dimensions.  Any help would be greatly appreciated.  See below for a table with sample data from the fact table, followed by my measures. 

PriorMonEAC = CALCULATE(SUM(Bookings[eacrv]),DATEADD('Calendar'[Date],-1,MONTH))

CurrEAC = SUM(Bookings[eacrv])

NoPriorAmount = VAR prior = [PriorMoEACRev]

VAR currmon = [CurrEACRev]

RETURN IF(prior=0,[CurrEACRev],0)

Total New Bkgs = Total New Bkgs = VAR __table = SUMMARIZE('Services Lines',"__value",[New Bookings])

RETURN

IF(HASONEVALUE('Services Lines'[Line of Service]),[New Bookings],SUMX(__table,[__value]))

[wdx223_Daniel · Accepted solution]

NoPriorAmount =
SUMX (
    VALUES ( Project[Project] ),
    VAR prior = [PriorMoEACRev]
    VAR currmon = [CurrEACRev]
    RETURN
        IF ( prior = 0, [CurrEACRev], 0 )
)

[KBedor]

Thank you!!  That works.  Performance is slow, but the result is accurate.   I'm marking this as the solution, although if anyone has recommendations to improve performance that would be helpful.

[AlB]

Hi @KBedor 

Can you share the pbix?

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