Expectations for future (Start+End date and amount) how do i calculate expected income pr future day

[Rygaard]

Date table (6500 lines)

Sales lines table (2 mill lines, 65K lines that are offers)

In my Calender table i tried to make a sum pr day in a new Collumb.. but ran out of memory :

SalesThisDay = CALCULATE(SUM('Sales Line'[SL_Amount]),FILTER('Sales Header','Sales Header'[SH_PriceStartDate]< EARLIER(Calender[DateValue])),FILTER('Sales Header','Sales Header'[SH_PriceEndDate]>EARLIER(Calender[DateValue])),FILTER('Sales Line','Sales Line'[SL_Document_Type]=0))

(document_type=0 is sales offers only

I think i need a new table, but I really dont know what it sould inculde..

Excel example attached:

[Pragati11]

Hi @Rygaard ,

Assuming there a relationship between following tables:

1. Sales Header
2. Sales Line
3. Calendar

May be so many FILTER expressions are creating issues. Try something as below:

SalesThisDay =

CALCULATE(

SUM('Sales Line'[SL_Amount]),FILTER('Sales Header','Sales Header'[SH_PriceStartDate] < EARLIER(Calender[DateValue]) && 'Sales Header'[SH_PriceEndDate] > EARLIER(Calender[DateValue]) && RELATED('Sales Line'[SL_Document_Type]) = 0))

Thanks,

Pragati

[amitchandak]

@Rygaard , Are you distributing/copying data for each day, where is the future data?

if you just need to allocate of copy on all dates for the range

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Attached file is new table approach