cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Total current month if prior month value = 0

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.

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

1 ACCEPTED SOLUTION
Super User
``````NoPriorAmount =
SUMX (
VALUES ( Project[Project] ),
VAR prior = [PriorMoEACRev]
VAR currmon = [CurrEACRev]
RETURN
IF ( prior = 0, [CurrEACRev], 0 )
)``````
3 REPLIES 3
Super User
``````NoPriorAmount =
SUMX (
VALUES ( Project[Project] ),
VAR prior = [PriorMoEACRev]
VAR currmon = [CurrEACRev]
RETURN
IF ( prior = 0, [CurrEACRev], 0 )
)``````
Frequent Visitor

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.

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.