Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
and in power bi date slicer I need to display like below
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
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
Hi @Anonymous
If I use start date filed in date slicer it displays as
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
Hi @Anonymous
The Datasource is oracle table
1.Periodtable
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.
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
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?
HI @Anonymous
I have tried with all the possible Relationships between the period_Table and Customer Table.Nothing works out.
HI @Charu
I will share my file here: https://1drv.ms/u/s!AgUIGKdICZfnj1p5k--kOjLDw9py
Just change the source in Edit Queries to your file again if you want to add some more stuff.
What I did is that i created another table with a column based on your date filter column. A formula like this:
Dates = CALENDAR(FIRSTDATE(InvoiceDate[date filter]);LASTDATE(InvoiceDate[date filter]))
Then i connect this to your customer table and add that column to your datesbetween slicer. This makes it work.
Let me know that this is what you are looking for or if i missunderstand you. I think the problem has been that here only has been two dates here and that you need all the datesbetween somewhere for the slicer to work. If you only want the datefilter values then you have to have a list or something.
Br,
Gurrato
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |