cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

How to create Calculated column to get date values based on status in power bi

Hi Everyone,

 

please help me to create the calculated column to get the date values based on Status column values.

For example, in the table, I'm having data like below

startdate.PNG

and in power bi date slicer I need to display like below

enddate.PNG

the calculated column like = 

Datefilter = IF(datetable[Status]="YES",datetable[Startdate],datetable[EndDate])

if status is yes then from date should pick from startdate field and To date should pick the enddate column

The enddate column will always have sysdate value

 

help me how to achieve this

 

thanks inadvance

10 REPLIES 10
Highlighted
Resolver I
Resolver I

Re: How to create Calculated column to get date values based on status in power bi

Is it the slicer that isnt working or the calculated column ( that one looks fine ).

I think that you need to have the calculated column set to the right data type. In this case date to be able to have the between date slicer available.


Br,
Gustav

Highlighted
Post Patron
Post Patron

Re: How to create Calculated column to get date values based on status in power bi

Hi @gurrato

 

If I use start date filed  in date slicer it displays as 

sdatecol.PNG

in To-date how to pass the end date value otherwise system date value, as I'm using direct query mode I couldn't create the custom table, now as per my requirement how to create the calculated column.

 

whether that calculated column will filter my data if I create the relationship with other tables?

 

Date table is one table where start date, end date, qtr, month, status presents and other tables have date column so I have created the relationship between date table and then another few tables.

 

The Calculated column where I need to create is in the Date table 

Highlighted
Anonymous
Not applicable

Re: How to create Calculated column to get date values based on status in power bi

Hi @Charu

 

I you can suggest what is your data source, then that would help.

Highlighted
Post Patron
Post Patron

Re: How to create Calculated column to get date values based on status in power bi

Hi @Anonymous

The Datasource is oracle table 

1.Periodtable

dsorc1.PNG

2.customertable(table name)-- has some fields in addition date column which will be having April, may so and so data but in date filter i need a column where it should take from-date from the period table and todate as system date. 

 

enddate.PNG 

here in table visual for customer table, I have used the date filter from the period table, as it is in between Apr1st to Apr11 those records only should display in the table visual 

 dontdisp.PNG

Highlighted
Resolver I
Resolver I

Re: How to create Calculated column to get date values based on status in power bi

How does your relationship look like in Power BI and in Oracle. Seems like the relationship is not correct at the moment between date and customer. As to your calculated column it shouldnt be a problem to create one as you did in your first post here.

Can you see your relationships in Power BI at all at the moment?


Highlighted
Post Patron
Post Patron

Re: How to create Calculated column to get date values based on status in power bi

HI @gurrato

I have tried with all the possible Relationships between the period_Table and Customer Table.Nothing works out.

Highlighted
Resolver I
Resolver I

Re: How to create Calculated column to get date values based on status in power bi

HI @Charu

Can you share some data and upload a pbix so I can try it?

Br,
Gustav

Highlighted
Post Patron
Post Patron

Re: How to create Calculated column to get date values based on status in power bi

HI @gurrato

Please find the sample data and pbix

 

 Sample PBIX

Sample Data

 

Highlighted
Resolver I
Resolver I

Re: How to create Calculated column to get date values based on status in power bi

Hi @Charu

I have requested access and might be able to look in to it tonight.

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors