I've created a Solution containing Apps and Tables which will determine the cost of a product over time(cost per day).
I've created calculated columns in Dataverse for each month of the year where if certain conditions are met the result is (days * quantity * price)
eg. Januari 2022 = (31 * 5 * 500)
One of the problems I've been facing is when the start date is in the middle of a month meaning the number of days =
total number of days minus the number of days before start date
eg. Start date 12 Januari = ( (31 - DiffinDays(column_StartDate , 2022-01-01) ) * 5 * 500 )
When using the DiffinDays to get the result I'm able to fetch the startdate from the specific column but the first date of the month doesn't have a column for lookup and needs to be hard coded and I can't seem to be able to find the correct format for this?
Is this even possible or is there another way to get the first date of the month in calculated columns?