Rolling total where data rows are sporadic

[Anonymous]

Hi!  I have a Trial balance that provides the following output:

The Entry for March 2019 for "Deposits" should be 9,897.50 but, since there were no transactions in that month for that account, the balance isn't calculated/presented.

The Measure I'm using is otherwise fine, and pretty standard:
=calculate(Sum(Data[Amount]),FILTER(all('Calendar'[Date]),'Calendar'[Date] <=max(Data[Date])))

How can I modify this so that the balance for a period shows, even if there are no transactions in that period?

Thanks!

[v-easonf-msft]

Hi， @Anonymous 

Please modify the formula like below:

Result =
CALCULATE (
    SUM ( Data[Amount] ),
    FILTER (
        ALLEXCEPT ( Data, Data[Account name], Data[Account code] ),
        Data[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,
Community Support Team _ Eason

[Anonymous]

Thanks, Eason!  Unfortunately, when I put that in , I get all zeros:

[amitchandak]

@Anonymous , This should show for each period, provided

1. Period is coming from calendar table

2. join of calendar table with fact/table is single directional

3. Calendar table is marked as Date table 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

[Anonymous]

Hi Amit, and thanks for your reply!

1.  The period is coming from a calendar table:

2. Join is single-directional

3. Calendar table was NOT marked as a date table - I fixed that, but still the same issue.

The data type for the Date field of the calendar table is Date:

The date table is made from a query that references the original "data" table it is linked to - could that be an issue?  Here's the m code to make the calendar table:
= {Number.From(List.Min(Data[Date]))..Number.From(List.Max(Data[Date]))}