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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ScottyR
Frequent Visitor

Custom column or M query to select value based on before or after 2nd Friday of each month

I have a table that includes monthly transaction data across multiple projects. Each month there are values for "actuals" and "forecasts". I would like to create a new column using power M query or DAX custom column features in PowerBI. This column should equal the value in either the "Actual" or "Forecast" column based on the following rule.

 

The 2nd Friday of the month is the trigger to switch from using "forecast" to "actuals" in the prior month. So, it should use the "forecast" value for September 2020 until the 2nd Friday in October 2020. After that, it should use the "Actual" value.

 

In the same table, I have a "year" column, "month" column, and a "date_key" column in the format of yyyymmdd. Example below.

 

So assuming the date is October 21, beyond the 2nd Friday of October, so it should use actuals for Sept and Forecast for Oct...

ProjectDate_KeyActualForecastsCustom  
120200701102010  
120200801102010  
120200901102010  
120201001102020  
220200901102010  
220201001102020  
220201101102020  

 

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

@ScottyR 

Has the problem been solved? If so, you may accept the appropriate post as the solution or post your own solution to help other members find it quickly. Thanks.

 

Best Regards,
Community Support Team _ Jing Zhang

MFelix
Super User
Super User

Hi @ScottyR ,

 

Try the following code:

 

if #date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1)
<=
#date(
    Number.FromText(
    Text.Start(Number.ToText([Date_Key]),4)), 
    Number.FromText(
    Text.Start(Text.End(Number.ToText([Date_Key]),4),2)),
    
    1 ) then [Forecasts] else if  #date(
    Number.FromText(
    Text.Start(Number.ToText([Date_Key]),4)), 
    Number.FromText(
    Text.Start(Text.End(Number.ToText([Date_Key]),4),2)),
    
    Number.FromText(
    Text.End(Number.ToText([Date_Key]),2)) )  <


Date.StartOfWeek(#date(Date.Year( DateTime.LocalNow() ),Date.Month(DateTime.LocalNow()),14),Day.Friday) then [Actual] else [Forecasts]

 

Be aware that you may need some adjustements because the current date is already afte the second friday.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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