Making the count at the end of the month = the count at the beginning of the next month

Question & Answer
,
1

I have data that displays assets as of the end of each month. My goal is to be able to show the growth of assets for any given month. The problem is, because I only have data for one day each month, the calculated field I have now cannot tell me the growth if I select only the month of April or only the month of May. I would need to select a start date of April 30th and an end date of May 31st to get the growth for the month of May. Is there a way to create a calculated field that will make the count of assets at the end of the month = the count of assets at the beginning of the next month. For example, the count of assets on 4/30 is 10 and on 5/31 is 15. I need the count of assets on 5/1 to also be 10. That way, I can select just the month of May on my bar chart and see that assets increased by 5.

2

Hi,

Create a calculated field with below definition,
truncDate(“MM”, Date_column)

This will give the first day of the column month

1 Like
3

Hi @Vignesh_Kannan - I’ve created that calculated field already; the problem is getting the system to recognize that the assets on that first day of the month should = the assets from the previous month end.

4

Hello @mif what about trying to use the table calculation functions (lag in this case) for this?

For example, for a dataset like this:

date assets
10-09-2022 2500
23-08-2022 2000
21-07-2022 1000
20-06-2022 800
20-05-2022 700
21-04-2022 600

If you create a calculated field like this:

assets variation:

sum(assets) - lag(sum(assets), [{date} ASC], 1)

You should get the following:

date assets assets variation
10-09-2022 2500 500
23-08-2022 2000 1000
21-07-2022 1000 200
20-06-2022 800 100
20-05-2022 700 100
21-04-2022 600

Hope it helps, please mark this solution as solved if that’s the case also to help other members of the community., otherwise let us know.

Thank you for posting into the QS community!

1 Like
5

Hi all- the lag function could be one option - have the period over period functions been tried? wouldnt it work here?

1 Like
6

@mif -
I’m marking the question as solved with one of the two approaches provided. If you still need assistance, please let us know.