If then else statement

[jalaomar]

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!

[amitchandak · Accepted solution]

@jalaomar , Create a column like this and use

coalesce([ActualDate],[ScheduleStartDate])

or a measure

maxx(table, coalesce(table[ActualDate],table[ScheduleStartDate]))

[v-kkf-msft · Accepted solution]

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.

[jalaomar]

Many thanks @v-kkf-msft !

The measure works great 🙂

[amitchandak · Accepted solution]

@jalaomar , Create a column like this and use

coalesce([ActualDate],[ScheduleStartDate])

or a measure

maxx(table, coalesce(table[ActualDate],table[ScheduleStartDate]))

[jalaomar]

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

[v-kkf-msft · Accepted solution]

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.

[jalaomar]

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

[v-kkf-msft]

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

[jalaomar]

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!!

[v-kkf-msft · Accepted solution]

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]

Hi @v-kkf-msft 

much better, thanks!!

It work as intended 🙂 

Best regards,

Jala