topic Re: Total current month if prior month value = 0 in DAX Commands and Tips

Total current month if prior month value = 0

Anonymous — Fri, 06 Nov 2020 21:00:03 GMT

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]))

Re: Total current month if prior month value = 0

AlB — Sat, 07 Nov 2020 12:14:18 GMT

Hi Anonymous

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

Re: Total current month if prior month value = 0

wdx223_Daniel — Mon, 09 Nov 2020 01:44:28 GMT

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

Re: Total current month if prior month value = 0

Anonymous — Mon, 09 Nov 2020 15:33:35 GMT

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.