Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ibutur
Frequent Visitor

Dates in period for a dynamic period

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?

1 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

View solution in original post

6 REPLIES 6
neatdot
Helper I
Helper I

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
Employee
Employee

@ibutur

 

I adjust the relationship like below.

Dates in period for a dynamic period_1.jpg

 

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

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

Dates in period for a dynamic period_2.jpg

 

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 )
)

Dates in period for a dynamic period_3.jpg

 

Best Regards,

Herbert

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:

 table2.PNG

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.

@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

Amazing, so far it looks good! Thank you!

ibutur
Frequent Visitor

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.