Selecting the time passed from a date

[Anonymous]

I´m getting asked a weird requisite. We have a database containing customer who are in debt. Each row is a different customer, and each customer has a paying deadline, in a date column. I´m getting asked to be able to select the time passed from that deadline date. 

For example, select 2 months after the deadline, or 5 months, or a year. Am i explaining right? Is this possible?

[v-angzheng-msft]

Hi, @Anonymous 

A simple and fast way is to use relative date slicer or filter pane.

what if parameter:

Create and use what-if parameters to visualize variables in Power BI Desktop

In addition,, if you use the way @amitchandak  mentioned above,

calculate(countrows(table), filter(Table, Table[Deadline] <= date(year(today()), month(Today()) -[whatif measure] , day(today()) ))

you need to be aware that, for example, if it is now December and you select the last two months, you get all dates less than or equal to October, not the dates of both November and December. And when the current month is January, you will get an error if you assume the parameter is greater than the current month.

You may need to modify the above measures, for example like the following

Measure = 
//Assuming the deadline is the latest date
var _maxDate=CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
var _selected=
IF(SELECTEDVALUE('Table'[Date])>EOMONTH(_maxDate,-[Parameter Value])&&SELECTEDVALUE('Table'[Date])<=_maxDate,
    1,0)
return _selected

Then filter the measure equal to 1 in the filter pane.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much for your detailed explanation and the attached report, i´ve learned new things and i really appreciate it. However, i dont think it´s doing what i need. To be more specific, next to my dates column in my dates table, i have a column doing a DATEDIFF which counts the numbers of days between that date column and today.

What i need is to create a visualization which is capable of changing the second date introduced in the formula DATEDIFF. Instead of doing the DATEDIFF between that date column and today, do it betweet that date and a selected period in the visualization. That´s exactly what i need. Is it possible? For example, selecting "2 months" in a visualization (that´s what comes into my mind right now) and doing the DATEDIFF between the date column and two months after that date.

Sorry for my poor explanation in the first post, and thanks again.

[v-angzheng-msft]

Hi, @Anonymous 

If I understand your question correctly, then you can create a measure instead of a calculated column, because the measure will be dynamic.
You can create a measure as follows:

Measure 2 = 
var _currentDate=MAX('Table'[Date])
var _DATEDIFF=DATEDIFF(_currentDate,EOMONTH(_currentDate,[Parameter Value]),DAY)

return _DATEDIFF

Result:

Please refer to the attachment below for details. Hope this helps.

If this is not what you need, could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.  It makes it easier to give you a solution.

If you reply to my post, since I am about to leave work now, I will read your reply during working hours tomorrow.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

We´re getting very close. The only thing which is failing (i think) is the parameter value. Instead of a number, it should be a date, right? Or at least, a value which adds X period of time to the first date in the DATEDIFF function.

For example, I select "2 months" in a visualization, the DATEDIFF is made between the dates column and that date 2 months after. If the date is 03/05/2011, the DATEDIFF should be calculated between that date, 03/05/2011, and 03/07/2011, that same date two months after.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Create a calendar table and use the date of the calendar table as a slicer. And control the slicer to single selection.

Then to create a measure as follows:

Measure 3 = 
var _datediff=DATEDIFF(MAX('Table'[Date]),SELECTEDVALUE('calendar'[Date]),DAY)
var _if=IF(HASONEVALUE('calendar'[Date]),_datediff,"Only one date can be required")

return _if

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Assume you need 2 months passed the deadline, For that, you can have a what if parameter.

calculate(countrows(table), filter(Table, Table[Deadline] <= date(year(today()), month(Today()) -[whatif measure] , day(today()) ))

[Anonymous]

How should the "whatif" measure be done?