Forum Discussion
Resolver IExpectations for future (Start+End date and amount) how do i calculate expected income pr future day
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))
I think i need a new table, but I really dont know what it sould inculde..
Excel example attached:
2 Replies
- amitchandak
Super User
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-or-end-date/ba-p/1503785
Attached file is new table approach
- Pragati11
Hi Rygaard ,
Assuming there a relationship between following tables:
- Sales Header
- Sales Line
- 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
