Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

If formaula

Hi i need a if formaul to calcualte how much a client should pay. 

 

I need to calculate tthe total hours for the current month, for  certain category. 

 

Current Monthth Fee= Calaculate(

Sum(Query1[Total Hours]),

Filter(Quer1[LPM Code])= P3 &&

IF Query1[Total Hours] <10 , "No charge"

IF Query1{Total Hours] >=10 && Query1{Total Hours]<15, "7,500"

IF Query1{Total Hours] >=15 && Query1{Total Hours]<20, "10,000"

IF Query1{Total Hours] >=20 && Query1{Total Hours]<30, "15,000"

IF Query1{Total Hours] >=30 && Query1{Total Hours]<40, "20,000"

IF Query1{Total Hours] >=40 && Query1{Total Hours]<50, "25,000"

IF Query1{Total Hours] >=50 && Query1{Total Hours]<60, "30,000"

Need Help
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    Anonymous , You could try below code for it:-

    Current Monthth Fee =
VAR _sum_hours =
    CALCULATE (
        SUM ( Query1[Total Hours] ),
        FILTER ( Quer1, Quer1[LPM Code] = P3 && Month(Quer1[LPM Code]) = Month(Today()) )
    )
RETURN
    SWITCH (
        TRUE (),
        _sum_hours < 10, "No charge",
        _sum_hours >= 10
            && _sum_hours < 15, "7,500",
        _sum_hours >= 15
            && _sum_hours < 20, "10,000",
        _sum_hours >= 20
            && _sum_hours < 30, "15,000",
        _sum_hours >= 30
            && _sum_hours < 40, "20,000",
        _sum_hours >= 40
            && _sum_hours < 50, "25,000",
        _sum_hours >= 50
            && _sum_hours < 60, "30,000"
    )

3 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous ,

     

    If I have understand your question correctly then below would be your ideal code:-

    Current Monthth Fee =
VAR _sum_hours =
    CALCULATE (
        SUM ( Query1[Total Hours] ),
        FILTER ( Quer1, Quer1[LPM Code] = P3 )
    )
RETURN
    SWITCH (
        TRUE (),
        _sum_hours < 10, "No charge",
        _sum_hours >= 10
            && _sum_hours < 15, "7,500",
        _sum_hours >= 15
            && _sum_hours < 20, "10,000",
        _sum_hours >= 20
            && _sum_hours < 30, "15,000",
        _sum_hours >= 30
            && _sum_hours < 40, "20,000",
        _sum_hours >= 40
            && _sum_hours < 50, "25,000",
        _sum_hours >= 50
            && _sum_hours < 60, "30,000"
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Samarth_18 Thank you, your formual worked, however i need it to be for the current month. I'm not quite sure where to add that part. 

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous , You could try below code for it:-

        Current Monthth Fee =
VAR _sum_hours =
    CALCULATE (
        SUM ( Query1[Total Hours] ),
        FILTER ( Quer1, Quer1[LPM Code] = P3 && Month(Quer1[LPM Code]) = Month(Today()) )
    )
RETURN
    SWITCH (
        TRUE (),
        _sum_hours < 10, "No charge",
        _sum_hours >= 10
            && _sum_hours < 15, "7,500",
        _sum_hours >= 15
            && _sum_hours < 20, "10,000",
        _sum_hours >= 20
            && _sum_hours < 30, "15,000",
        _sum_hours >= 30
            && _sum_hours < 40, "20,000",
        _sum_hours >= 40
            && _sum_hours < 50, "25,000",
        _sum_hours >= 50
            && _sum_hours < 60, "30,000"
    )