cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 136 members 1,630 guests
Please welcome our newest community members: