Forum Discussion

Tomhayw's avatar
Tomhayw
Icon for Helper I rankHelper I
3 years ago
Solved

Measuring time between dates in the past

Hello everyone,

 

I currently have a table with dates for invoices sent and invoices received.

I want to calculate how many invoices weren't paid for over 30 days in the past (I have the data available for this).

 

Essentially I want my output to be a line graph with the count of invoices that weren't paid within 30 days within each historic month. Is there a measure anyone can think of that would be able to do this?

 

Thanks,

Tom

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Tomhayw ,

    The project 3 should not be included due to the invoice paid on Feb 07, 2022... Am I right? I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

    Measure = 
VAR _year =
    SELECTEDVALUE ( 'Date'[Date].[Year] )
VAR _month =
    SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
VAR _seledate =
    EOMONTH ( DATE ( _year, _month, 1 ), 0 )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Project name] ),
        FILTER (
            'Table',
            'Table'[Invoice sent] < _seledate
                && 'Table'[Invoice paid] > _seledate
                && DATEDIFF ( 'Table'[Invoice sent], _seledate, DAY ) > 30
        )
    )
RETURN
    _count

    Best Regards

11 Replies

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

    Tomhayw , Last 30 without a date selection

     

    example measure  =
    var _max = maxx(allselected(date),date[date]) // or today()
    var _min = _max -30
    return
    CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

     

    but you want select a date an then want last 30 , then you need slicer on an independent date table

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = _max -30
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

    • Tomhayw's avatar
      Tomhayw
      Icon for Helper I rankHelper I
      3 years ago

      Hi,

       

      I tried your measure based on your guidance:

      23 11 test measure =
      var _max = maxx(ALLSELECTED(DateDim),DateDim[Date])
      var _min = _max - 30
      RETURN
      CALCULATE(count('Xero Merger w/ clockify and order dates'[Combined Project Names]),FILTER('Xero Merger w/ clockify and order dates', 'Xero Merger w/ clockify and order dates'[earliest invoice date] <= _max && 'Xero Merger w/ clockify and order dates'[earliest invoice date] >=_min))
       
      But it doesn't return anything on a line graph:

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Tomhayw ,

    Do you want to get a line chart and display the data with the count of the invoices which weren't paid within 30days? In order to get a better understanding on your requirement and give you a suitable solution, could you please provide some raw data in your invoice table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Tomhayw's avatar
      Tomhayw
      Icon for Helper I rankHelper I
      3 years ago

      Hi there,

       

      I've attached a screenshot of what the raw data looks like, and what an expected output would look like:

       

      Data:

      In these months, I want to see how many invoices were still outstanding which had >30 days with no invoice paid as of the end of each month

       

      Desired output: 

       

      I hope this provides some clarity on my problem

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Tomhayw ,

        Thanks for your reply. What's the calculation logic? Why it is 1 in Jan, 3 in Feb, 2 in March? Could you please provide the special examples to explain it base on your sample data. Thank you.

        Best Regards