Question

'Days in Month' Calendar

  • 29 August 2023
  • 1 reply
  • 179 views
M
Rank
Badge

I’d like to create a metric dimensionalized by Month with a Number data format. I want each cell to contain (1) If actuals, the number of days in that month (2) if the current month, the number of days from the start of the month to the current day (3) if forecast, zero.

 

Can anyone please advise on how to do this? I know that I will probably need an input cell with the current date since there is no equivalent of the excel TODAY function. I tried using the ‘DAYSINPERIOD’ formula but couldn't get Pigment to accept the formula / deliver a result.

1 reply

Fabien
Rank
Userlevel 3
Badge

Good morning,

You can do the following:

IF('Is Actual - Month',
  DAYSINPERIOD(Month),
  IF(Month = 'Current Month',
    DAYSINPERIOD(Month, Month.'Start Date', 'Current Date'.'Start Date'),
    0 // This is your forecast condition assuming you only have Actuals/Forecast.
  )
)

Where Current Date is a user input metric of type Day, and Current Month is a metric of type Month with formula ‘Current Date’.Month.

Hope that helps !

Reply


Ask your question here!

Terms & ConditionsCookie settings