Forum Discussion
Rolling total where data rows are sporadic
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!
4 Replies
- amitchandak
Super User
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-bd52912a5bd4Hi 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]))}
- v-easonf-msft
Community Support
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
