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
Anony_mous
Frequent Visitor

Issue in creating Calculated column for handling blank in Date column

Hi All,

I am new to Power BI I have a requirement like I have 2 columns from same table that is Start Date and End Date.Start Date and End Date are of text data type I need to convert it into date data type so that all months will appear in a sequential order for Example Jan 2022,Feb 2022,Mar 2022,Apr 2022,May 2022,.......,Jan 2023,Feb 2023,Mar 2023. Where I need to combine the dates in 2 column(Start Date and End Date) into 1 column by taking the distinct of 2 columns. Since I have the blank in both the columns Start Date and End Date. I am getting the error like( in table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table)

this.As of now I am trying to create a separate table for combined Start Date and End Date.Again I am getting the same error I wrote above.Since both Start Date and End Date are calculated column I cannot do any logic on this in Power Query.So I need to work using DAX .Please give some solution to handle this.All suggestions are welcome.Thank you in advance!!
Regards,

Anony_mous

1 REPLY 1
amitchandak
Super User
Super User

@Anony_mous , you can handle blank like

 

Date St = if(isblank([Start Date]) || [Start Date] = "" , blank(), [Start Date])

 

for having months consider date/calendar table. In this case with active/inactive join or both inactive joins

 

Calendar = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

 

 

refer to how to work with two dates in the blog, video, and attached files

 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

 

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.