Show Headcount based on Date Range Selected

Ankur

Hello Everyone,

I have been breaking my head over last 2 weeks looking for a probable solution to a scenario that I have. I tried ChatGPT as well but nothing worked.

So, I have a Headcount App where I show Headcount based on various dimensions like - Geo, Region, Country, Gender, Leader, Job Role, etc, etc.

Currently the Headcount is showing for the Current Month as I have selected 'This Month' option. And whenever user select a past Date, then my cards goes blank. Changing the Date Range from 'This Month' to 'All Time' does not help, as it sum up all the month Headcount and shows an inflated number, which is not expected.

What I am looking for is a Dynamic Beast Mode which will Sum the Headcount based on the date selected. If No Date is Selected then it will show the most Recent Headcount, else it will show Headcount as per the date selected.

In the below attached Excel file, I have created a simulation to show what exactly I am looking for. Please choose a date in cell E2 and it will change the numbers. Please Note the Geo is just an example I have given.

Dynamic Headcount.xlsx

I have created a Beast Mode and it does the work that I wanted but it splits the Geos.

Beast Mode I created :

CASE 
  WHEN    
    `Date` = MAX(`Date`) OVER (PARTITION BY `Geo`)        
    THEN SUM(`Headcount - Actual`)
  ELSE 0
END

Output I got is :

Ideal Output required :

I am in desperate need of a solution to handle this scenario.

Data_Devon

Hello Ankur!

A Beastmode might be an unnecessary solution here.

You can set the "Geo" (or other categorical data) on the X Axis and the "Count" of a date on your Y Axis.

You can then select whatever date you want in the date selection of the Chart itself.

This will default the date range for an untouched card.

Then, you can create a filter card to allow end users to select the date that they want to look at. This filter card does not need to be powered off a Beastmode, but rather can use the same data that is powering the output chart.

NB: If you date data is at a daily level but you want filtering ability at the month level, then a simple Beastmode can group those dates up to the month level, like so:

MONTH(`Date`)

Selecting default date range on chart:

Slicer/Filter using date field: