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 all,
in need for some help and hopefully someone can support 🙂
currently i have two columns, ActualDate & ScheduleStart Date
in my Matrix table i would like create a measure to display for example
Contract --> if ActualDate is availble display this date but else display ScheduleStartDate or blank if no data availble in the two columns
Does someone know how this measure could be stated?
THANKS!
Solved! Go to Solution.
@jalaomar , Create a column like this and use
coalesce([ActualDate],[ScheduleStartDate])
or a measure
maxx(table, coalesce(table[ActualDate],table[ScheduleStartDate]))
Hi @jalaomar ,
Try the following formula:
Measure =
SWITCH(
MAX('Table'[BaselinePurpose]),
"Contract", MAXX('Table', coalesce('Table'[ActualStart],'Table'[ScheduleStart])),
"Current", MAX('Table'[ActualStart]),
"ActualSchedule", MAX('Table'[ActualStart])
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jalaomar ,
Try the following formula:
Measure_Date =
IF(
MAX('Table'[Actual Start]) <> BLANK(),
MAX('Table'[Actual Start]),
MAX('Table'[Schedule Start])
)
Actual/Schedule vs Contr =
DATEDIFF(
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "Contract"),
_Measure[Measure_Date]
),
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
'_Measure'[Measure_Date]
),
DAY
)
Actual/Schedule vs Curr =
DATEDIFF(
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "Current"),
_Measure[Measure_Date]
),
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
_Measure[Measure_Date]
),
DAY
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
@jalaomar , Create a column like this and use
coalesce([ActualDate],[ScheduleStartDate])
or a measure
maxx(table, coalesce(table[ActualDate],table[ScheduleStartDate]))
Hi @amitchandak, Many thanks!
is it possible to put a condition to the measure?
in my rows i have
Contract Baseline
Current Baseline
Actual/schedule
for contract baseline i would like to use the measure you created
for Current baseline i would like to only show actual dates according to the tollgate the project is currently in (example ITG2 and blank in the following tollgates)
for Actual/schedule date i only want to visualize the Actual date accodring to which tollgate the projects is in currently (example ITG2)
Possible to put these conditions in plase?
Thanks!
Best regards,
Jala
Hi @jalaomar ,
Try the following formula:
Measure =
SWITCH(
MAX('Table'[BaselinePurpose]),
"Contract", MAXX('Table', coalesce('Table'[ActualStart],'Table'[ScheduleStart])),
"Current", MAX('Table'[ActualStart]),
"ActualSchedule", MAX('Table'[ActualStart])
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-kkf-msft
to follow up on this topic i am wondering if you could maybe support me to create measures that calculate the delta for the different baselinePurpose as shown in below picture?
deviation between Actual/Schedule vs Contract baseline
deviation between Actual/Schedule vs contract baseline
Many thanks!
BR
Jala
Hi @jalaomar ,
I don't know your model, so I create some sample data. Then create measures:
Actual/Schedule vs Contr =
DATEDIFF(
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "Contract"),
'Table'[Date]
),
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
'Table'[Date]
),
DAY
)
Actual/Schedule vs Curr =
DATEDIFF(
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "Current"),
'Table'[Date]
),
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
'Table'[Date]
),
DAY
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Hi @v-kkf-msft Thanks for looking into this
the measure is not displaying as i would like to but the calculation is correct.
for BaselinePurpose
Contract
Current
Actual/schedule
I need to take into considiration that if there is a "ActualStart" date then the measure should be based on that date else take the "ScheduleStart" Date
se below picture of a little sample of the dataset
I will visialize the measure in a matrix table where the
BaselinePurpose is the rows and Tollgate is the columns and the measure is the value
Hope this made it a bit more clear, otherwise let me know
Many thanks!!
Hi @jalaomar ,
Try the following formula:
Measure_Date =
IF(
MAX('Table'[Actual Start]) <> BLANK(),
MAX('Table'[Actual Start]),
MAX('Table'[Schedule Start])
)
Actual/Schedule vs Contr =
DATEDIFF(
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "Contract"),
_Measure[Measure_Date]
),
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
'_Measure'[Measure_Date]
),
DAY
)
Actual/Schedule vs Curr =
DATEDIFF(
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "Current"),
_Measure[Measure_Date]
),
MAXX(
FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
_Measure[Measure_Date]
),
DAY
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |