Calculation to set the previous month's total as current month actual value

Hi everyone,
I have to create a calculation to set the current value of a measure. based on the following input.

  1. Here active headcount is pulling from db as of now
  2. Selected Month is Aug 2024, which is a parameter and the visual displays next 12 months of selected month.
  3. Hires= hires(pulling from db)+Hire(Slider value)
  4. Terms=(terms(Pulling from db)+Terms(Slider value)
  5. Expected Ending Headcount= Active Head Count+ Hires+ Terms
    As mentioned in the figure, I need to create a calculation for Active headcount, where the 1st month show case the actual value taken from db. and the next month should show case the Expected Ending Count of Previous month as Active headcount.
    Can anyone help to write the proper calculation for getting this based on the selected date?

Hi,

Try this document, hope this will work for you.

Regards,
Naveed Ali

Hi,

Just look at solution, may be you get some help from it.

Regards,
Naveed Ali

Hi @Naveed , @DylanM

I tried that approach, but its not working in my case. may be something wrong with the calculation. But I am sure, this wont be enough for me
I need to satisfy the following criteria’s to get a proper calculation

  1. For the First month in the visual, I need to check what is the StartDate Selected, then I have to return the Active headcount for the next month of startdate(Since we are showcasing next 12 months)
  2. Next I need to check, for the 2nd Month in the visual, I need to return the Expected Ending Headcount of 1st Month.
  3. Then for the 3rd month in the visual, I need to return the Expected ending headcount of 2nd month and so on.
    This is the calculation I tried, and I only got one month data.
    image

I guess we have to add the calculations to return the next month active headcount based on the selected month in start date filter, then for remaining, I have to return the previous month, expected ending count.

Could you please help to frame the calculation for this?

Hi all,
Is there any update on the above question? I have been trying different types of calculations to get the previous month expected ending count as next month active for past 1 week. But cant see any improvement. Can anyone help please!!!

Hi @Ganga,

When you need help with calculations, our recommendation is to create a sample in Arena. This will improve your chances of getting help from the community.

Regards,
Arun Santhosh
Pr QuickSight SA

1 Like

Hi @ArunSanthosh I was able to get the calculation using Lag function.

1 Like

Hi @Ganga ,

Glad to hear that you are unblocked :slight_smile:

Regards,
Arun Santhosh
Pr QuickSight SA