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

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.

      • Pmorg73's avatar
        Pmorg73
        Post Patron
        5 years ago

        Thanks for helping.

        screen grab. I recreated my datediff in dax, so both a calc column and dax give me the correct month timeframe difference which is great.

         

        If you see I have 4 entries for "40 Browns Bay", but I am after just a single value of the newest entry. So in this case it would be 28/7/20 and 6 months old. I do not need the 3 previous entries to show up. I just want to indicate when was the last time the job was worked on a time entry made in the system, and then sum up all of the "WIP" value for that job. If I remove the month age column it aggregates like this. What i want is just then a single column entry per job with the age of the last entry date only. I need to stop it showing all of the entries in that job. does that make sense?