Forum Discussion
Solved
Current Month Day No Forecast
Hi All, I'm doing forecast figures for some projects and I have been asked that in the current month (in this case March 2022) when the date is below the 15th the forecast figure should be Previo...
How about now? Tomorrow it will be 485 😁
Measure = SWITCH ( TRUE(), AND ( SELECTEDVALUE('Date'[Month_no]) < MONTH(TODAY()), SELECTEDVALUE('Date'[Year]) <= YEAR(TODAY()) ), SUM('Budget Actual'[Actual Cost]), --Before Active Month AND ( SELECTEDVALUE('Date'[Month_no]) > MONTH(TODAY()), SELECTEDVALUE('Date'[Year]) >= YEAR(TODAY()) ), [Actual and forecast], --After Active Month TODAY () < DATE ( YEAR(TODAY()), MONTH(TODAY()), 15 ), [Actual and Forecast PM], -- Active Month, Before 15 SUM('Budget Actual'[Actual Cost]) -- Active Month, After 15 )
coskuersanli I think we are sooo close! However, Feb and March should have the same number now (200) until its 15th of March then march changes to March Actual which is 485. It's a very tricky one.
Only the current month picks the Previous Month's value when it's not yet the 15th of the month. Past months should have Actuals for the month and future month have forecast (we have no problem with forecast at the moment).
Thanks so far.
How about now? Tomorrow it will be 485 😁
Measure =
SWITCH
(
TRUE(),
AND
(
SELECTEDVALUE('Date'[Month_no]) < MONTH(TODAY()),
SELECTEDVALUE('Date'[Year]) <= YEAR(TODAY())
),
SUM('Budget Actual'[Actual Cost]), --Before Active Month
AND
(
SELECTEDVALUE('Date'[Month_no]) > MONTH(TODAY()),
SELECTEDVALUE('Date'[Year]) >= YEAR(TODAY())
),
[Actual and forecast], --After Active Month
TODAY () <
DATE
(
YEAR(TODAY()),
MONTH(TODAY()),
15
),
[Actual and Forecast PM], -- Active Month, Before 15
SUM('Budget Actual'[Actual Cost]) -- Active Month, After 15
)
coskuersanli Thank you so so much. It worked! I have to change my Month No to wholenumber instead of text then Boom!