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 ,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?

 

 

 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Pmorg73 

     

    According to your description, I think you can modify your measure to calculate the desired result.

    Like this:

    last date =
MAXX (
    FILTER ( 'Table', [job summary] = SELECTEDVALUE ( 'Table'[job summary] ) ),
    [date]
)
    No of months old = -DATEDIFF(TODAY(), [last date], MONTH)

    Here is my sample .pbix file.Hope it helps.

    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.

    • Pmorg73's avatar
      Pmorg73
      Post Patron
      5 years ago

      Thanks for the help and the logic. It does not seem to be working for me. I have a seperate date table, but using the measure I can not select the date column in that, only in my fact table.

      data:

      measure used:

       

      result:

       

      model:

       

      any thoughts?

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        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.