Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

If formula

Hi I have two measures to calculate the YTD depending on where you are in the world. 

 

US Associates is callaculated using=

ASYTD Billable Hours = CALCULATE(SUM(Utilisation[ChargeableHours]), DATESBETWEEN('Work Date'[Date], DATE(2020,11,1), DATE(2021,10,31)))
 
Everyone one esle is calculated using = 
YTD Billable = CALCULATE(SUM(Utilisation[ChargeableHours]),FILTER('Work Date', 'Work Date'[Calendar Year]="2021"))
 
I currently have them on sepearte tabs but I want to create one table that can have both. I need help with a measure that if your region is America and title is Associate or Senior Associate, it will calculate the ASYD Billable Hours and for everyone else YTD Billable Hours
Need Help

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    Anonymous 

    Are you looking for something like this?

    New Measure =
IF (
    SELECTEDVALUE ( Table[Region] ) = "America"
        && SELECTEDVALUE ( Table[Title] ) IN { "Associate", "Senior Associate" },
    [ASYD Billable Hours],
    [YTD Billable Hours]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Fowmy 

       

      Thank you, I'm just confused on what what SelectedValue should be. 

      • Fowmy's avatar
        Fowmy
        Super User
        4 years ago

        Anonymous 

         

        You wanted to check if the the current selection or region and title and then execute the relevant measures I thought. Do you have slicers for them ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Fowmy

     

    Im' writing it out 

     

    If YTD = IF(
    'WFE"[Current Region Name]= "America" && 'WFE

     

    I'm getting an error I'm not sure what should go before the table & column

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi  Anonymous  ,

    When you create a measure ,you should try like below:

    If YTD = IF( selectedvalue('WFE"[Current Region Name])= "America" ,successful result,error result)

    or

    If YTD = IF( max('WFE"[Current Region Name])= "America" ,successful result,error result)

     

    And if questions still not solved ,could you pls share your pbix file ?

     

     

    Best Regards

    Lucien

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi  Anonymous ，


    Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

     

    Best Regards
    Lucien