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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Tomhayw ,

You can consider to create another date dimension table and don't create any relationship with your fact table. And apply the date field onto your visual to replace the original date field...

If it still not working, could you please share a simplified pbix file with me? You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards