Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pmorg73
Post Patron
Post Patron

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?

 

 

1 ACCEPTED SOLUTION

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.

View solution in original post

13 REPLIES 13
amitchandak
Super User
Super User

@Pmorg73 , Try a measure like

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

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

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

Thanks for helping.

Pmorg73_0-1610423376485.png

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?

 

Pmorg73_1-1610423638392.png

 

 

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)

1.png

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.

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:

Pmorg73_0-1610924220619.png

measure used:

Pmorg73_1-1610924299338.png

 

result:

Pmorg73_2-1610924350308.png

 

model:

Pmorg73_3-1610924387838.png

 

any thoughts?

 

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.

Its curious. I can not get it to work

measure

Pmorg73_0-1611097337924.png

 

result

Pmorg73_1-1611097353668.png

I am still getting a result for each entry

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.

9.png

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.

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.

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

 

Pmorg73_2-1611108223570.png

 

this is the section of the model

Pmorg73_3-1611108278159.png

 

 

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

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.