Dates in period for a dynamic period

[ibutur]

Hi guys,

I have the following setup:

1 table: First date of sale: Contains columns with First date of sale (date) and Product Code (text)

1 table: Sales: Contains Product code (text), Date of sale (date) and Quantity of units (number)

1 table: Calendar: Contains unique dates from 2014 onwards

1 table: Product code: contains unique product codes

In terms of relationships, both Sales and First day are refering to Product code and Calendar respectively.

What I'm trying to do is understand how many units each product code sold within the first week. This I'm trying to do by using DATESINPERIOD 

CALCULATE(sum('Sales'[Units Sold]); FILTER('First day of sale';

DATESINPERIOD('BI sales'[Date.Date Calendar];'First day of sale'[First day of sale];7;DAY))

However, for this I get the following error: 

A single value for column 'First day of sale cannot be determined'. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.

But I can't use an aggregation on a date, can I? I  can't put in an average date or something like that. And I can't use a single date, because for each product the starting date is different. 

What would you propose as a workaround for this?

[neatdot]

Very late, I know, but here is an alternative solution:

Sold within the first week = 
CALCULATE (
    SUM ( 'Sales'[Units Sold] ),
    GENERATE (
        VALUES ( 'First day of sale'[First day of sale] ),
        DATESINPERIOD ( 'BI sales'[Date.Date Calendar], 'First day of sale'[First day of sale] ), 7, DAY)
    )
)

[v-haibl-msft]

@ibutur

I adjust the relationship like below.

Then create a FirstDay column in Sales table with following formula.

FirstDay = 
RELATED ( 'First date of sale'[First date of sale] )

Now we can create a measure to get the expected result. I’ve also upload my PBIX file here for reference.

Sold within the first week = 
CALCULATE (
    SUM ( Sales[Quantity of units] ),
    DATESINPERIOD ( 'Calendar'[Dates], LASTDATE ( Sales[FirstDay] ), 7, DAY )
)

Best Regards,

Herbert

[ibutur]

Hi Herbert,

Thanks a lot for this, it's an eye-opener. However, there a detail qbout the aggregation that confuses me. When I apply this and create weeks 1, 2, 3 and 4 using the formula, and then create a table separated by product style, it seems to work good.

However, the total number at the bottom makes no sense:

 

This number isn't a sum or a count of all rows. When I export to csv, I can get actual sums and counts.

This is a problem when I try to get an aggregated overview for all of these, so I can understand overall trends. I've tried it in your file and similarly, when I remove the style code, I only get 68, which isn't sum total of all, but just your sales for last product.

[v-haibl-msft · Accepted solution]

@ibutur

Please try with following updated measure.

SUM_Sold within the first week = 
SUMX (
    Sales,
    CALCULATE (
        SUM ( Sales[Quantity of units] ),
        DATESINPERIOD ( 'Calendar'[Dates], LASTDATE ( Sales[FirstDay] ), 7, DAY )
    )
)

Best Regards,

Herbert

[ibutur]

Amazing, so far it looks good! Thank you!

[ibutur]

Hi guys,

I have the following setup:

1 table: First date of sale: Contains columns with First date of sale (date) and Product Code (text)

1 table: Sales: Contains Product code (text), Date of sale (date) and Quantity of units (number)

1 table: Calendar: Contains unique dates from 2014 onwards

1 table: Product code: contains unique product codes

In terms of relationships, both Sales and First day are refering to Product code and Calendar respectively.

What I'm trying to do is understand how many units each product code sold within the first week. This I'm trying to do by using DATESINPERIOD 

CALCULATE(sum('Sales'[Units Sold]); FILTER('First day of sale';

DATESINPERIOD('BI sales'[Date.Date Calendar];'First day of sale'[First day of sale];7;DAY))

However, for this I get the following error: 

A single value for column 'First day of sale cannot be determined'. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.

But I can't use an aggregation on a date, can I? I  can't put in an average date or something like that. And I can't use a single date, because for each product the starting date is different. 

What would you propose as a workaround for this?