InMemory Measure to calculate daily sales in a period
Hello. Somebody must have come across this...
The InMemory database is based on Dynamics NAV data. We have a measure that computes the Sales Amount from within the Value Entry table. Making the sum of the Sales Amount for a given period of data, say a month is fairly straigthforward.
However, we would like to be able to compute the Average Daily Sales Amount for any period. To do so, we need to divide the Sales Amount by the number of calendar day in the period. We tried to create a measure that DISTINCT COUNT the number of posting date, but if there are no sales on a given date, it is excluded from the count. We also tried to use max(Posting Date)-min(Posting Date), but again, if there are sales only between two min and max dates in the period, we don't have a correct count.
Has someone found a way to accurately count the number of days within any given period as a measure in the data model?
Any suggestion would be much appreciated!