Limit overall timeline dynamically to periods with transactions

[PowerBI-ker]

Hi community,

I built a data model that allows to analyze data regarding cost centers. Every cost center has a lifetime budget, i. e. it's not a monthly or yearly budget. Every month we track expenses, and the difference between the lifetime budget and the cumulative expenses is the remaining budget.

The data model has 14 million rows, and it starts April 1st 2006. The calculated numbers are correct and they show correctly in the corresponding periods. However, the lifetime budget shows for all periods (because it's not linked to any particular period).

The screenshot below shows this situation, obviously there's a lot of wasted space (and it looks terrible in Power BI charts)

Here's my question: How could I enforce that the measures "Lifetime Budget" and "Remaining Budget" are only displayed between the earliest and and the latest date of the "Expense" measure (in this example, between Q1-2019 and Q4-2020?

These are the measures:

Lifetime Budget :=
SUM ( FCC[FCC_Grant_Amount] )

Expense:=
CALCULATE (
SUM ( JournalEntries[Amount_] ),
JournalEntries[JE Entry Type] = "Posted",
LEFT ( JournalEntries[GL_Account], 1 ) = "2",
LEFT ( JournalEntries[GL_Account], 2 ) <> "21",
JournalEntries[GL_Account] <> "29900000",
FCC[Entity_Type] = "PI"
)

Cumulative Expense :=
CALCULATE (
SUM ( JournalEntries[Amount_] ),
JournalEntries[JE Entry Type] = "Posted",
LEFT ( JournalEntries[GL_Account], 1 ) = "2",
LEFT ( JournalEntries[GL_Account], 2 ) <> "21",
JournalEntries[GL_Account] <> "2122013",
JournalEntries[GL_Account] <> "21510701",
JournalEntries[GL_Account] <> "29900000",
FCC[Entity_Type] = "PI",
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

Remaining Budget:=
IF (
[Grant Amount] = 0
|| ISBLANK ( [Grant Amount] ),
BLANK (),
[Grant Amount] + [Grant Cumulative Expense] + [Grant Commitments]
)

Thanks a lot for any input!

[PaulDBrown · Accepted solution]

@PowerBI-ker 

How about:

Show Lifetime Budget = IF(ISBLANK([Expense]), BLANK(), [Lifetime Budget]])

Show Remaining Budget = IF(ISBLANK([Expense]), BLANK(), [Remaining Budget]])

[PowerBI-ker]

Thank you very much Paul, that did the trick! I had a trickier solution in mind (finding the first and the last date when there were actual expenses), but that's clearly an overkill here. Your suggestion works perfectly in both Excel and Power BI.

Much appreciated!