Skip to main content
cancel
Showing results for 
Search instead 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

Reply
smpatric
Frequent Visitor

TTM Calculation Failing with 0 Values

Hello,

 

I have a 12 Month Rolling Total Calculation (see below) that tracks the number of deals brought in on a monthly basis. The calculation works correctly until I try to slice by the Source fact, which allows the user to drill down into the employees that brought the deals in. The trouble seems to be that not all Sources bring in Deals every month, thus the value for that month would be 0, which causes the calculation to revert to the earliest value it can find (in this case, I'm only visualizing the previous 3 years, so it currently reverts to December 2016). This causes incorrect data to be displayed as well as odd spikes in the line graph that tracks the running total. Any idea why the 0 value would through off this calculation?

 

# Deals TTM = CALCULATE([Deals],ALL('Origination Date'[Date], 'Origination Date'[Month Year]),ALLEXCEPT(Deals,Deals[Platform/Add-On],Deals[Tier],Deals[Source Tier (Groups)],Deals[Sources]),DATESBETWEEN('Origination Date'[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Origination Date'[Date]))),LASTDATE('Origination Date'[Date])))

 

1 REPLY 1
smpatric
Frequent Visitor

After looking at the problem further, it looks like the calculation isn't reverting to a previous value, but summing all values when a given month is 0. I can't seem to figure out why the calculation works so long as there are values being added on a monthly basis. 

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.