Running Total with blank value not cumulating values

[Elofly]

Hello,

I have build up this formula to retrieve Running Totals for the amount of hotel chains signed in the past years:

Running Total Hotel Chains Signed =

CALCULATE(

    DISTINCTCOUNT(tbHotelChains[IdHotelChain]),

    FILTER(

        ALL(tbHotelChains),

        tbHotelChains[CommercialNotificationDate]<= MAX(tbHotelChains[CommercialNotificationDate])

    )

)

However the results I get is the following:

Year	Month	Count of IdHotelChain	Running Total Hotel Chains Signed
2022	January	1	1933
2022	February	1	1934
2022	March		1872
2022	April		1872
2022	May	4	1938
2022	June	4	1942
2022	July	1	1943
2022	August	1	1944
2022	September		1872
2022	October		1872
2022	November		1872
2022	December		1872
2021	January	3	1907
2021	February	6	1913
2021	March	4	1917
2021	April	4	1921

So that when data is BLANK, the running total is getting bacl to previous values, how can I avoid that?

Thanks

[v-zhangti]

Hi, @Elofly 

I simply simulated some data in the hope of restoring your problem, but it didn't work out.

Can you tell us more details about your source data?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

In the visualization and in the measure, please try using year column and month column from a dim-calendar table, instead of using those from the fact table.

Thanks.