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
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
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.