I have a situation where we are delivered a monthly sales report each morning. In the report there is a start date and and end date column. Most records are "daily" where the start date and end date are the same. If I did a datediff the result would be zero (0). There are some, however, where the start date and end date span more than one, so datediff > 0. If this happens, what I need to do is to add rows for each day (if the datediff is 3 then it would add three rows) and in those rows DIVIDE the sale amount by that number. I would post a sample of the data, but this is PHI (HIPAA) data.
An example would be: the start date is 10/01/2023 and the end date is 10/04/2023. DateDiff (or actually I am using = Table.AddColumn(#"Changed Type1", "DayDiff", each Duration.Days([Service Thru Date]-[Service From Date])) The difference is 3. If the billable total for those three days is $900, I need three rows generated - one for each date and then the billable total for each of those rows would be $300. So $900/3 days = $300.
Is this possible through the PowerBI report, or through Power Query?
Thanks for the information
