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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

If else then statement on a date slicer

Hi Guys,

 

I have a date slicer and my client would like to have a table that shows the CONTRACTID, SIGNATUREDATE AND EXPERATIONDATE.

 

iIam not able to create a date slicer and to show if a user click on the date of today from date slicer, the signaturedate must be +1 and experationdate must be -1. Please check the underneath image for example.

 

Any suggestions? Many thanks.

 

 

2018-01-16 10_15_53-Book1 - Excel.png

1 ACCEPTED SOLUTION

You just need a function to calculate it based on your date. My date field is called sql_date just replace your date.

 

Selector = if(Query1[sql_date]=date(year(now()),month(now()),day(now())),"Today",if(Query1[sql_date]=date(year(today()-1),month(today()-1),day(today()-1)),"Yesterday","Other"))

View solution in original post

11 REPLIES 11
gooranga1
Power Participant
Power Participant

I would unpivot my underlying data table and create a new table with columns

 

Contract ID          DateType             Date

1                          SignatureDate     10/1/2018

1                          ExpirationDate     31/12/2018

 

Then you could add a calculated date column that added 1 day to type SignatureDate and -1 to ExpirationDate you could then use this new calculated column to filter.

 

You could use a matrix table to display the data grouped by contractid with date types across the top.

Anonymous
Not applicable

@gooranga1,

 

Thanks for your idea.  how can i create a column DateType (the one that you mentioned)  that only return SignatureDate or ExpirationDate?

 

Screenshots below on how to unpivot and then add a date column based on +1 day for signature date and -1 day for expiration date.

 

unpivot.PNG

 

unpivot1.PNGunpivot2.PNG

Anonymous
Not applicable

Hello @gooranga1,

 

Thanks for your response. Unfortunatly your solution didnt work.

 

Please check the screenshot below. 

The field dateselector didnt gave any results.

 

Please help.

 

 

 

2018-01-19 11_48_09-Settings.png

hi @Anonymous,

 

Not sure hwat you mean by having no valuyes, mine has values and I can't see what your drop down looks like if you click it? Your date selector values and the actual values are the same in that screen shot? I thought you wanted to add and subtract a date depending on the attribute ExpirationDate and SignatureDate?

 

 

 

date selector.PNG

Anonymous
Not applicable

Hi @gooranga1,

 

Thanks for your quick response. would yo be so kind to send your .pbix file ? 

 

Thanks !

Hi @Anonymous,

 

I can't attach pbix files in here. What is the formula in your calculated field? Mine is;

 

DateSelector = dateadd('Table4 (2)'[Value].[Date],if('Table4 (2)'[Attribute]="SignatureDate",1,-1),DAY)

where your should read;

 

DateSelector = dateadd('CUSTDIRECTDEBITMANDATES'[Value].[Date],if('CUSTDIRECTDEBITMANDATES'[Attribute]="SignatureDate",1,-1),DAY)

You must also ensure that the "Value" column in your new table is formatted as a "Date" in the "Modelling" tab on the main ribbon.

Anonymous
Not applicable

Hello @gooranga1,

 

 

My apologize, i was confussed.

The new requirement must be a table that contains

 

 

1. all the contract id's that have the signaturedate before the date of today

2. all the contract ids's that have the experationdate after the date of today.

 

 

Thanks !

 

 

 

@Anonymous,

 

Then you don't need to pivot the data then just add a formula on the original table;

 

Select Contracts = if(Table4[SignatureDate] < now() && Table4[ExpirationDate] > now(),1,0)

Then filter on that calculated column and it will select all the contracts that meet that criteria. In this instance if you don't need both criteria to be true you could replace "&&" with "||" to create an OR statement instead you can filter on.

 

sigdates.PNG

Anonymous
Not applicable

Hi @gooranga1,

 

Thanks for this solution, you have solved my problem.

 

Next requirement is when this table is shown , my client would like to interact with a slicer that contain, the date of today, date of yesterday  and other day? See the image below.

 

 

2018-01-19 15_35_45-Power BI – Did you know ALL Power BI Services Servers are in UTC_ Now how to han.png

 

 

 

You just need a function to calculate it based on your date. My date field is called sql_date just replace your date.

 

Selector = if(Query1[sql_date]=date(year(now()),month(now()),day(now())),"Today",if(Query1[sql_date]=date(year(today()-1),month(today()-1),day(today()-1)),"Yesterday","Other"))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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