Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
So thanks to @Eric_Zhang I was able to rank my projects according to time (link below)
What I need now is a measure that will calculate the difference in the time of one event and the next in days for the same project. Example below (for the last event the difference will be the difference between now and that event). I have added a phony column called NextEventTime to explain my point.
Project - EVENTID - TIME - NEXTEVENTTIME - TIME DIFF - RANK
A - 1234 - 1/1/2017 - 1/7/2017 - 6 - 1
A - 2345 - 1/7/2017 - 1/14/2017 - 7 - 2
A - 3456 - 1/14/2017 - 9/28/2017 - 257 - 3
B - 7890 - 6/1/2017 - 6/7/2017 - 6 - 1
B - 8901 - 6/7/2017 - 6/14/2017 - 7 - 2
B - 9012 - 6/14/2017 - 9/28/2017 - 106 - 3
C - 6543 - 9/1/2017 - 9/28/2017 - 27 - 1
D - 9876 - 8/1/2017 -1
D - 7865 - 8/7/2017 -2
Solved! Go to Solution.
Hi @moizsherwani,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi @moizsherwani,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
@v-jiascu-msft Thanks so much for your response. This doesn't work in Direct Query Mode though. Did you have that in mind when providing the proposed solution?
@v-jiascu-msft Thanks so much for your response. This doesn't work in Direct Query Mode though. Did you have that in mind when providing the proposed solution?
Hi @moizsherwani,
I think a calculated column would be a good solution. But you still can try it as a measure.
1. Calculated column.
1) One step.
TimeDiffOnestep = VAR currentProject = 'Table1'[Project] VAR currentTime = 'Table1'[TIME] VAR nextEventTime = CALCULATE ( MIN ( 'Table1'[TIME] ), FILTER ( 'Table1', 'Table1'[Project] = currentProject && 'Table1'[TIME] > currentTime ) ) RETURN IF ( ISBLANK ( nextEventTime ), TODAY () - currentTime, nextEventTime - currentTime )
2) Two steps.
NextEventTime = VAR currentProject = 'Table1'[Project] VAR currentTime = 'Table1'[TIME] VAR nextEventTime = CALCULATE ( MIN ( 'Table1'[TIME] ), FILTER ( 'Table1', 'Table1'[Project] = currentProject && 'Table1'[TIME] > currentTime ) ) RETURN IF ( ISBLANK ( nextEventTime ), TODAY (), nextEventTime )
TimeDiffTwoSteps = [NextEventTime] - [TIME]
2. As a measure.
MeasureSolution = VAR currentProject = MIN ( 'Table1'[Project] ) VAR currentTime = MIN ( 'Table1'[TIME] ) VAR nextEventTime = CALCULATE ( MIN ( 'Table1'[TIME] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Project] = currentProject && 'Table1'[TIME] > currentTime ) ) RETURN IF ( ISBLANK ( nextEventTime ), DATEDIFF ( currentTime, TODAY (), DAY ), DATEDIFF ( currentTime, nextEventTime, DAY ) )
Best Regards!
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |