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

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: