If else then statement on a date slicer

[Anonymous]

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.

[gooranga1]

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]

@gooranga1,

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

[gooranga1]

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.

[Anonymous]

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.

[gooranga1]

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?

[Anonymous]

Hi @gooranga1,

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

Thanks !

[gooranga1]

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]

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]

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

[Anonymous]

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.

[gooranga1 · 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"))