topic Re: Resetting running totals AND keep previous totals for missing months in DAX Commands and Tips

Resetting running totals AND keep previous totals for missing months

Anonymous — Fri, 20 May 2022 01:51:51 GMT

Hello everyony,

So I have a date dimension and a transaction table as the below:

1-Date Dim

Date	Month.N	Fin.Year	Prd.Name
1/01/2020	Jan	2019-20	P07 19-20
...	...	2019-20	...
23/06/2021	Jun	2020-21	P01 21-22

2- Transaction Fact

Reporting Date	Location	Value
30-Jun-20	X	6
31-Aug-20	Y	7
30-Jun-21	Z	2

I created a measure to calculate running totals and show the missing months numbers from the previous month total as the below:

MEASURE =

var maxd=CALCULATE(MAX(Transaction[Reporting Date]),ALLEXCEPT(Transaction[Location]))

return CALCULATE(SUM(Transaction[Value]),

FILTER(ALLSELECTED('Date Dim'), 'Date Dim'[Date] <= MIN('Date Dim'[Date]) && MIN('Date Dim'[Date]) <= maxd))

It's working and calculating runing totals of missing months as the below:

Fin.Year	Reporting Date	MEASURE
2019-20	30-Jun-20	6
2019-20	31-Jul-20	6
2019-20	31-Aug-20	13
2019-20	\|	\|
2019-20	\|	\|
2019-20	\|	\|
2019-20	31-May-20	88
2020-21	30-Jun-21	90

I need to keep the missing months totals but RESET them based on the Financial Year field.

PLEASE HELP.

Appreciate it

Re: Resetting running totals AND keep previous totals for missing months

lbendlin — Sat, 21 May 2022 22:16:42 GMT

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

Re: Resetting running totals AND keep previous totals for missing months

v-cazheng-msft — Wed, 25 May 2022 07:05:17 GMT

Hi Anonymous,

You may try this Measure.

Missing months totals based on FY = CALCULATE([runing totals of missing months],VALUES('Date Dim'[Fin.Year]))

Then, the result looks like this.

Also, attach the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know how. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun