Forum Discussion
Helper IIICalculate sum on a given date
I have a crossjoined table with all dates and all my articles and then I have one column where I store all my transactions, plus and minus (which do not occur every day).
Through this measure:
Okey, so I found an really ugly way of solving it, but at least it works..
I added a table and just entered 2022-12-31 in one cell, then created a new measure where I calculated the previous measure and put that the date should be equal to the sum in that new table..
Don't really understand why I couldn't say that date = 2022-12-31 but yeah.. it works now at least. But there might be a nicer solution..
End of Year = CALCULATE([SOH]2, JB[CalenderDate]=SUM('EndOfYear'[Column1]))
9 Replies
- lbendlin
Super User
Do you want to use the built in forecasting functions or do you want to create your own? Which algorithm are you planning to use? Where should it start? Do you want it to be adaptive to the seasonal changes?
vipettHi,
The forecast is calculated elsewhere.
In this report I only have all historical transactions and future forecasted transactions per day. The measure I have today accumulates these numbers to show what stock levels I had a specific day and what stock levels I will have on a specific day in the future.
But yeah, the future demand is pre-calculated elsewhere and I am only seeking the accumulated number here.
- Ashish_Mathur
Hi,
There should be a Calendar Table with a relationship (Many to One and Single) from the CalendarDate column of the JB table to the Date column of the Calendar Table. Write this measure
Measure = calculate(SUM(JB[ConsumptionTTL]),datesbetween(Calendar[Date],date(2022,12,31),date(2022,12,31)))
Hope this helps.
- vipett
Hi,
This only genereates an empty table 😞 (did however write the date as 2022-12-31)
My source table "ConsumptionTTL" only contains actual numbers and since this is in the future, the measure I use today calculates an accumulated value based on the previous transactions.
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file.
Hi vipett ,
Please see if this Blog helps.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
- vipett
It can't see that it provides a solution for my exact question, I see how ever that I could potentially get some help and clean up the graph, currently it looks like this, where the black line is actual and it continues straight after today's date. The blue line is the forecast. Ideally I want the black line to stop on today's date.
- vipett
Okey, so I found an really ugly way of solving it, but at least it works..
I added a table and just entered 2022-12-31 in one cell, then created a new measure where I calculated the previous measure and put that the date should be equal to the sum in that new table..
Don't really understand why I couldn't say that date = 2022-12-31 but yeah.. it works now at least. But there might be a nicer solution..
End of Year = CALCULATE([SOH]2, JB[CalenderDate]=SUM('EndOfYear'[Column1]))
