Forum Discussion

BGB's avatar
BGB
Icon for Helper II rankHelper II
4 years ago
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...
Need Help
Show and Tell
Tips and Tricks
  • coskuersanli's avatar
    coskuersanli
    4 years ago

    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
	)

     

BGB's avatar
BGB
Icon for Helper II rankHelper II
4 years ago

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. 


  • coskuersanli's avatar
    coskuersanli
    Icon for Resolver III rankResolver III
    4 years ago

    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
	)

     

    • BGB's avatar
      BGB
      Icon for Helper II rankHelper II
      4 years ago

      coskuersanli Thank you so so much. It worked! I have to change my Month No to wholenumber instead of text then Boom!