Forum Discussion

Pmorg73's avatar
Pmorg73
Post Patron
5 years ago
Solved

Datediff using months

Hi hopefully a quick query for a Guru.

 

i created a calculated column for an age in months as follows:

No of months old = -DATEDIFF(TODAY(), '01_Calendar'[Date], MONTH)
 

It works out the value correctly for me. No problem so far.

 

The issue I have though is I am using it in a table in which I have a "last entry date" column.

I want it to sum all the entries by a job reference and return the oldest date entry only.

I am finding that it is splitting the output for each individual time entry date. So for example

I get 5 entries of job number 1, but I want it return just one value of the oldest.

Its an Aged WIP table basically.

 

My last date entry measure is:

Last WIP entry date = calculate(
MAX('12 WIP Ledger'[Ledger Date]), LASTDATE('12 WIP Ledger'[Ledger Date] ))

 

 

any ideas?

 

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Pmorg73 

     

    Sorry for not being able to reply you in time due to busy Monday.

    If you use a calendar table date column in your visual, you can easily modify your measure.

    Like this:

    Measure =
MAXX (
    FILTER ( 'Table', [job summary] = SELECTEDVALUE ( 'Table'[job summary] ) ),
    RELATED ( 'Table 2'[Date] )
)

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Pmorg73 , Try a measure like

    calculate(countrows(Table), filter(Table, Table[Ledger Date] = calculate(Min(Table[Ledger Date]), allexcept(Table, Table[JOB ID])))

    • Pmorg73's avatar
      Pmorg73
      Post Patron
      5 years ago

      you have calulate both sides of the equal sign? makes no sense

       

      I tried to use both as two measures. First string returns the incorrect number. And the second string uses two columns. "Multiple columns cannot be converted to scalar value"

       

      Back to the drawing board I think with this one

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Pmorg73 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.