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

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

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

    Essentially what I mean is that if I was to look in Feb-22, how many of those projects had invoices that still weren't paid at the end of the month and where the number of days since invoice sent > 30.

    So for Feb, project 1, 2 and 3 still hadn't been paid by the end of February, and the number of days since the invoice sent to the end of February >30, so you would count those three.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

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

        Yes you are correct re project 3 not being included. I've tried to replicate this measure in my PBI file but again the incorrect values are being returned.

        My date dimension table and invoice received date have a relationship between them. Is this potentially why?

         

        EDIT:

        Here is what my actual table looks like (sensitive data redacted):

        And this is the relationship with date dimension table:

        Note that the relationship joins invoice received date to the date dimension.

         

        I hope this provides greater clarity