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.

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.

  • Pmorg73's avatar
    Pmorg73
    Post Patron
    5 years ago

    No wrroies, I really appreciate your help btw

     

    The Total WIP is a measure: I spot that I used ALL for another report so that I can could get a total ignoring the date slicing I had.......I will quickly check a new measure that is just sum

     

     

  • Pmorg73's avatar
    Pmorg73
    Post Patron
    5 years ago

    Its curious. I can not get it to work

    measure

     

    result

    I am still getting a result for each entry

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

    Hi, Pmorg73 

     

    Since you didn‘t provide sample data and files, the sample I made is to reproduce the content of your screenshots, and there may be some situations that are not mentioned and cause problems.

    Is the 'total all wip' a measure? Can you provide the formula? Since you didn’t specify, I used it as a column directly in my sample.

    You need to provide more information so I can help you.

     

    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

    This is my data table (its quite large and I dont want to share it as its business info)

     

     

    this is the section of the model

     

     

  • Pmorg73's avatar
    Pmorg73
    Post Patron
    5 years ago

    having drilled in. it appears when I add the "Age in Months" calculated column from my date table it is when it adds the multiple values. So I think you have solved my initial problem thank you. i will play around with a max value for the date AGE column as it is there that the problem lies.

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

    Hi, Pmorg73 

     

    Because the specific context is not clear,the result may be some unexpected circumstances. Anyway, I am glad you can modify it yourself.

    If you have other questions, please feel free to ask me.

     

    Best Regards

    Janey Guo