Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Selecting the time passed from a date

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?

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      How should the "whatif" measure be done? 

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-angzheng-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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.