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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Tomhayw ,

      Please remove the relationship between your fact table and Datedim table, and check if you can get the correct result. If no, please provide more raw data in your tables (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 without sensitive info. 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 thanks,

        This seems to have worked but I really need a relationship between the date and fact table as I am undertaking other analysis on this dataset. Is there a way I can incorporate both?

         

        Thanks.