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.
Hi, I have a report from a SQL database that I need to pull dates from on the progression between phases for each project. Each project has multiple phases, and multiple action dates within the phase. Ultimately, I need to find the progression of each project through the phases in # of days (see examples from excel).
final graph example:
To do this I need the first date at a certain phase (PHASE_START), then the count of days between that phase’s start date and the next phase’s start date (or start date to last action date if it's the last phase in a project). Then, these counts need to be added together to show the progression of the project through each phase from 0 to the number of days from the very start to the latest action date of all the phases in that project.
The PHASE_START is fairly easy to get, but I’m getting stuck on counting the days between the start dates of different phases as there are multiple entries for each combination of project, phase, and action date. Any help would be greatly appreciated, thank you!!
Solved! Go to Solution.
Hi, @ccj
According to your description, I create a sample to meet your needs.
First you need to create a distinct table to display phase and start column, I also create a date table for sort.
Then you need to create a measure to calculate the date interval.
Note: Put the fields of different tables in the line chart will report errors, but they will be displayed normally after putting measure in values.
Like this:
Table 2 =
UNION (
SUMMARIZE (
Table1,
Table1[PHASE],
"date",
MINX (
FILTER ( ALL ( Table1 ), [PHASE] = EARLIER ( Table1[PHASE] ) ),
[ACTUIN_DATE]
)
),
ROW ( "PHARE", "start", "date", DATE ( 2021, 1, 1 ) )
)
Measure =
VAR filtertable =
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE] = SELECTEDVALUE ( 'Table 2'[PHASE] )
)
VAR mindate =
MINX ( filtertable, [ACTUIN_DATE] )
VAR maxdate1 =
MINX (
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE_START] > mindate
),
[PHASE_START]
)
VAR maxdate2 =
IF ( ISBLANK ( maxdate1 ), MAXX ( filtertable, [ACTUIN_DATE] ), maxdate1 )
RETURN
DATEDIFF ( mindate, maxdate2, DAY ) + 0
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Hi, @ccj
According to your description, I create a sample to meet your needs.
First you need to create a distinct table to display phase and start column, I also create a date table for sort.
Then you need to create a measure to calculate the date interval.
Note: Put the fields of different tables in the line chart will report errors, but they will be displayed normally after putting measure in values.
Like this:
Table 2 =
UNION (
SUMMARIZE (
Table1,
Table1[PHASE],
"date",
MINX (
FILTER ( ALL ( Table1 ), [PHASE] = EARLIER ( Table1[PHASE] ) ),
[ACTUIN_DATE]
)
),
ROW ( "PHARE", "start", "date", DATE ( 2021, 1, 1 ) )
)
Measure =
VAR filtertable =
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE] = SELECTEDVALUE ( 'Table 2'[PHASE] )
)
VAR mindate =
MINX ( filtertable, [ACTUIN_DATE] )
VAR maxdate1 =
MINX (
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE_START] > mindate
),
[PHASE_START]
)
VAR maxdate2 =
IF ( ISBLANK ( maxdate1 ), MAXX ( filtertable, [ACTUIN_DATE] ), maxdate1 )
RETURN
DATEDIFF ( mindate, maxdate2, DAY ) + 0
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
hi @v-janeyg-msft, this is exaclty what i was looking for!!! thank you for providing the sample, really helped.
I followed your directions and modifed the sample formula to my data, and i'm running into one big issue - the numbers on my chart are counting backwards.
the line progression is correct, the x-axis is in the correct order, the difference between each point is correct, but it counts from 64 to 1 when it should be 1 to 64. I have the y-axis inverted to show the line having the updwards progression, but ideally the y-axis should start at 0 and increase, but with this line. Is there a way i could modify the measure formula to count correctly?
When i put the measure in the table, it seems to be counting backwards starting frome the most recent date, then on and on to the first date giving the start date the highest value which is incorrect.
Hi, @ccj
Check the setting in Y axis format.: invert axis
If the result is incorrect, please provide your code and related data, otherwise it is difficult for me to judge.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Hi @v-janeyg-msft, i have the y-axis inverted on purpose. the line is correct when inverted, but the values count backwards which is wrong. When the y-axis isn't inverted it looks like this:
this line is wrong and still counts backwards. i want it to look like the graph below, but the data points should count up from 1 to 64:
thanks for al your help!
I want to help you..But you just provide two screenshots, it's useless... I don’t even know what data you put on the X axis.
If it’s just the wrong order, you can sort it. I also used date column to sort the newly created table in the sample.(if you know sort by column and carefully check my sample)
If the result is incorrect, please provide your code and related data, otherwise it is difficult for me to judge.
Best Regards,
Community Support Team _ Janey
Hi,
Create a simple table visual and drag Project and Phase to the row labels. Write these measures
First action date = min(data[action_date])
Last action date = max(data[action_date])
Duration = 1*([Last action date]-[First action date])
Format duration as a whole number.
Switch the vidual to a line visual.
Hope this helps.
@Greg_Deckler thank you for the prompt response and forwarding me to the MTBF article! It seems very similar to what i'm doing. When following the MTBF process in my data, however, i'm having an issue with the end date (RepairCompleted in the article) as my data has a start date but no end date. That needs to reference a different phase start to essentially get that value (as the end date is not the last date listed for that phase).
I was able to use that formula format you pasted in the previous reply, but it returns "12/30/1899" in every row. If i change the [Value] reference, then i get an issue that text can't be compared with number values.
thanks for your help
@ccj Seems like you could use an MTBF approach. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |