Resetting running totals AND keep previous totals for missing months

[Anonymous]

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

[v-cazheng-msft · Accepted solution]

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

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

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