InMemory Measure to calculate daily sales in a period

Why not create a measure that returns the datediff of 2 dates if you use calendar dates? This won't work if you're using working days, but with calendar days it should.

Thanks for your input Femke. Could you elaborate on which two dates you would use? In the fact table, we only have one date which is the date at which the sales was posted.

In the example below, you see that the day count [max(posting date)-min(posting date) +1] gives a day count of 1 and 26 for January. This can significantly distorts the data. Use the DISTINCT COUNT is even worst.

Hi how is your datamodel setup? Do you have a calendar tabel with all dates and then a fact with posting date and customer id and the sales value? Then you can create in your dwh a measure with count distinct calendar days within a month. Or another way: have in the calendar tabel an extra field  with the max posting days in that month (eg MaxPostingDaysInMonth). So every day record in January holds 20, every record in February holds 19. Create a measure in olap to get the max of that field , so  max(MaxPostingDaysInMonth) and name it #PostingDays, next you can create either in olap (Sum(Sales Value)/max(MaxPostingDaysInMonth) or in Targit  Sales Value / #PostingDays the metric you want.

Just rember that you can only diplay this by Month, if you need Year, Quarter, Week  you need separate fields in calendar tabel for that.

This presumably that you want the same number of posting dates for all clients.

Thanks much for your input Femke. The datamodel looks like this:

The calendar table is dPeriod and the fact table is fValueEntry. The other dimension tables contains information about the items sold and where they were sold from.

I tried what I think you suggested by

1. Creating a new cube based on the dPeriod table, which generally is a dimension table but in order to
2. Create a measure which is a distinct count of TheDate, it also needs to be a cube.

However, this new measure cannot be added to the fValueEntry cube (that contains Sales Amount) nor the Master Virtual Cube because it is linked from fValueEntry TO dPeriod. So I tried to create a copy of dPeriod, called fdPeriod to create the new measure, but then it only works on giving me day counts for the total:

My guess is I would need to create a table that contains all the date-item-location combination in order to link fdPeriod to those other tables. In our environment, it is 6 000 000 lines PER DAY! To have 2 full years of data, it requires over 4 billions line!

Other ideas?

I actually tried creating all combinations for 2 years and the ETL throws :

10:55:04.452 ERROR : Could not add SQL select TheDate, ItemNo, VariantCode, LocationCode
10:55:04.453 from tItemLocation, tPeriodSubset from ME as table fPeriod Error executing SQL select TheDate, ItemNo, VariantCode, LocationCode
10:55:04.454 from tItemLocation, tPeriodSubset Error Executing QuerySystem.AggregateException: One or more errors occurred. ---> System.IndexOutOfRangeException: Index was outside the bounds of the array.

Hi Sylvain,

I think you should be able to create the Daycount on the dPeriod table instead of on the fValueEntry.

If you create a cube on the dPeriod and make row count on that table and use that for you calculation it should include all days regardless of it has sales on the day or not...

This was an important hurdle we had to overcome in order to calculate the budgeted sales per day.  We needed working days.  So we created a measure the Data Model calculating working days such as: 

case when Dayofweek(DIM_JournalPeriod.thedate) in (1,7) then 0 else case when DIM_JournalPeriod.thedate in ('05/31/2021','07/05/2021','09/6/2021','11/25/2021','12/23/2021','12/24/2021','12/31/2021','01/03/2022') then 0 else 1 end end  

The individual dates are our holidays.

Boe Pedersen, I created a cube on dPeriod, as well as a measure on TheDate (DISTINCT COUNT), but I was not able to add any shared dimension on that cube. The result is that the count is OK for the totals, but any other dimensions does not have a count... what am I doing wrong?

Jay Scott, pretty much same question, how do you link the Budget Sales measure to the DIM_JournalPeriod? Also, do you have Budgeted Sales for every work date?

Thanks all for your input!

I created a cube for DIM_JournalPeriod to place the measure.  The only shared Dimensions in the cube is the date dimensions from DIM_JournalPeriod.  Dim_JournalPeriod is linked to Budget Sales by TheDate field.  We have Budgeted Sales by Month.  I use the No of Business Days Measure to calculate budgeted Sales per day.  See attached screenshot.