Passing Date Filter into Pivot Formula

[mbottai]

Hello!

I was wondering if anyone has passed in a date parameter into a Pivot Table Formula before. If so, how best can I accomplish this? 

I have a begin count of employees and end count. There is a count for every day and this report can be run off of any date range. 

I want to see the Begin Count on the min day that is reflected in the dashboard filter. For the End Count I want to see the amount based off the max day in the dashboard filter. 

The formula below is counting each employee where Measure Reason is either begin count or end count and the last piece is where I want to add in the min/max date from the Date dashboard filter. 

Any thoughts or ideas would be greatly appreciated!

[Angelina_QBeeQ · Accepted solution]

Hi @mbottai ,

I have one idea how we can create this with a formula. But we need a duplicate of the "Date" field, which we use as a dashboard filter.
Fields that I used:

•  Date -  a field that is a dashboard filter.
•  Date_dup - a copy of the Date field. I used it for aggregation.
•  DateNum - int field for the date ("20221203").
•  ShipCountry - dimension field for the pivot.
•  Freight - field for the measure.

That's the sample of the formula for begin measure.

I have made an aggregation on the Date_Dub field and I'am trying to implement the following logic.
For example, we have the following selection in the dashboard filter:

And we have a table with values for each day:

If we go into the first ship country "Argentina" we should go through each date (Date_dup) and check, whether this date is equal to minimum date in the selection.
How do we find the minimum date in a selection?
Here we see this part where we find the min DateNum with all value filters for Date_Dup and for ShipCountry:

( min([DateNum]), All([ShipCountry]) , All([Days in Date_dup]) )

This is done to ignore the aggregation to get the entire dates.
So, in the case where the current date is not equal to the minimum date, it means that we do not have values for this dimension and that date, and we will get 0. Otherwise, we will get the freight.
For the end date, we have a similar formula:

Sum([Days in Date_dup],
if(
	max([DateNum])=
	(max([DateNum]),All([ShipCountry]),All([Days in Date_dup]))
	,sum([Freight])
  	,0
  )
)

Kind regards,
Angelina from QBeeQ
[email protected]

[mbottai]

Thank you Angelina!