cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
regasanyoto Member
Member

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

Accepted Solutions
gooranga1 Senior Member
Senior Member

Re: If else then statement on a date slicer

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 Senior Member
Senior Member

Re: If else then statement on a date slicer

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.

regasanyoto Member
Member

Re: If else then statement on a date slicer

@gooranga1,

 

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

 

gooranga1 Senior Member
Senior Member

Re: If else then statement on a date slicer

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

regasanyoto Member
Member

Re: If else then statement on a date slicer

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

gooranga1 Senior Member
Senior Member

Re: If else then statement on a date slicer

hi @regasanyoto,

 

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

regasanyoto Member
Member

Re: If else then statement on a date slicer

Hi @gooranga1,

 

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

 

Thanks !

gooranga1 Senior Member
Senior Member

Re: If else then statement on a date slicer

Hi @regasanyoto,

 

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.

regasanyoto Member
Member

Re: If else then statement on a date slicer

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 !

 

 

 

gooranga1 Senior Member
Senior Member

Re: If else then statement on a date slicer

@regasanyoto,

 

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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors