Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BGB
Helper II
Helper II

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 Previous Month Actual Cost, when equal to or above 15th of this current month it returns Actual Cost for March 2022, April onward display budget figure.

In the picture below, Actual and Forecast is the column/measure that needs changing. I have added Budget and Actual Cost in Variance measure to derive Actual and Forecast column, however, March shows 618 but should be 200 (Previous month actual cost) until its 15th of March then shows 485.. April going forward has the correct figure as they are.

BGB_0-1647247777132.png

 

Thanks in advance.

1 ACCEPTED SOLUTION

How about now? Tomorrow it will be 485 😁

 

coskuersanli_0-1647270790383.png

 

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
	)

 

View solution in original post

10 REPLIES 10
BGB
Helper II
Helper II

@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 😁

 

coskuersanli_0-1647270790383.png

 

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!

BGB
Helper II
Helper II

I think we've done it this time 🙂

 

I've added a month column to Date table with MONTH(Date[Date]) and changed the measure a little. (DATE function was wrong.)

Measure = 

SWITCH
	(
		TRUE(),
		AND
			(
                
				SELECTEDVALUE('Date'[Month_no]) > MONTH(TODAY()), 
				SELECTEDVALUE('Date'[Year]) >= YEAR(TODAY())
			),
		[Actual and forecast], 
		TODAY () <= 
			DATE
				(
					YEAR(TODAY()),
					MONTH(TODAY()),
					15
				),
		[Actual and Forecast PM],
		[Actual and Forecast]
	)

coskuersanli_1-1647262798933.png

 

 

coskuersanli
Resolver III
Resolver III

Hi @BGB ,

 

Can you try this please? 🙂

 

SWITCH
	(
		TRUE(),
		TODAY <= 
			DATE
				(
					15,
					MONTH(TODAY()),
					YEAR(TODAY())
				),
		previous_month,
		actual_forecast
	)

I've changed it a little, this should work.

 

SWITCH
	(
		TRUE(),
		AND
			(
				MONTH(dates_table[date]) > MONTH(TODAY()), 
				YEAR(dates_table[date]) > YEAR(TODAY())
			),
		budget, 
		TODAY <= 
			DATE
				(
					15,
					MONTH(TODAY()),
					YEAR(TODAY())
				),
		previous_month,
		actual_forecast
	)

 

 

@coskuersanli Thanks for looking at this.
When I tried this in a measure it won't accept 'Date'[date] and when I tried to use it as a column it didn't accept today
line 7..

BGB_0-1647254081385.png

 

 

BGB_1-1647254164797.png

 

Can you put () after TODAY and try again? 🙂

@coskuersanli Apologies if I'm starting to sound thick. I have left a screen short to this. I wonder why I'm not allowed to upload the PBIx file.

Picture 2 shows the switch didn't happen. Also, I had to use the date within my dataset as datetable date was not allowed (Didn't come up)

March 2022 should be showing PM figure.

The column formula

BGB_0-1647259021255.png

 

BGB_1-1647259089152.png

 

 

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.