topic Re: Rolling total where data rows are sporadic in DAX Commands and Tips

Rolling total where data rows are sporadic

Anonymous — Tue, 23 Aug 2022 23:53:11 GMT

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!

Re: Rolling total where data rows are sporadic

amitchandak — Wed, 24 Aug 2022 04:33:36 GMT

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

Re: Rolling total where data rows are sporadic

Anonymous — Wed, 24 Aug 2022 15:10:47 GMT

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

Re: Rolling total where data rows are sporadic

v-easonf-msft — Fri, 26 Aug 2022 09:19:40 GMT

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

Re: Rolling total where data rows are sporadic

Anonymous — Fri, 26 Aug 2022 16:23:03 GMT

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