Forum Discussion

jalaomar's avatar
jalaomar
Icon for Helper IV rankHelper IV
5 years ago
Solved

If then else statement

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 displ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    jalaomar , Create a column like this and use

     

    coalesce([ActualDate],[ScheduleStartDate])

     

    or a measure

     

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

  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

     

     

  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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's avatar
jalaomar
Icon for Helper IV rankHelper IV
5 years ago

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's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
    jalaomar
    Icon for Helper IV rankHelper IV
    5 years ago

    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's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        jalaomar
        Icon for Helper IV rankHelper IV
        5 years ago

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