The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
I have a data table with forecast data and one with actuals.
Sometimes they overlap (for example when i recive the actuals for October but we have not forecasted from November yet - I have both actuals and forecast for October...)
In that case I just want to exclude data for October from the forecast table - "the actual data overrides the forecast"
I have tried this approch
Meassure calculates the last month of actuals
Last actual date = lastdate(Actuals[Date])
Messure calculates forecast if date is lower than year end and bigger than "last actual date"
Forecast remaining of year = calculate(sum(Forecast[Sum of Quantity Ton]);and(forecast[date]>=[Last actual date];forecast[date]<=date(year(today());12;31)))
But I get this "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
I dont get it?
Please can you give me a hand? Or is there a smarter way of doing this? 🙂
Thanks
Martin
Solved! Go to Solution.
Hi @Anonymous
It complains because you are using the simplified syntax for the filter with something that is not allowed. To solve that issue ( and that issue only, I haven't looked at the rest), try:
Forecast remaining of year = CALCULATE ( SUM ( Forecast[Sum of Quantity Ton] ); FILTER ( ALL ( forecast[date] ); AND ( forecast[date] >= [Last actual date]; forecast[date] <= DATE ( YEAR ( TODAY () ); 12; 31 ) ) ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @Anonymous
It complains because you are using the simplified syntax for the filter with something that is not allowed. To solve that issue ( and that issue only, I haven't looked at the rest), try:
Forecast remaining of year = CALCULATE ( SUM ( Forecast[Sum of Quantity Ton] ); FILTER ( ALL ( forecast[date] ); AND ( forecast[date] >= [Last actual date]; forecast[date] <= DATE ( YEAR ( TODAY () ); 12; 31 ) ) ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
@AlB wrote:Hi @Anonymous
It complains because you are using the simplified syntax for the filter with something that is not allowed. To solve that issue ( and that issue only, I haven't looked at the rest), try:
Forecast remaining of year = CALCULATE ( SUM ( Forecast[Sum of Quantity Ton] ); FILTER ( ALL ( forecast[date] ); AND ( forecast[date] >= [Last actual date]; forecast[date] <= DATE ( YEAR ( TODAY () ); 12; 31 ) ) ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Worked like a charm!
Great suport
Thanks alot AIB!
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
7 | |
7 |