Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help Creating IF Statement

I'm trying to create a "simple" IF statement saying if the year is current then use another formula.  Below is my current formula:

 

hhh = COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(TKD[timekeeper_id]),
DATESBETWEEN(Dates[Date],MIN(Dates[Date]) ,MAX(Dates[Date]))),
[timekeeper_id],"ABCD",CALCULATE(SUM(TKD[Atty Hours]),DATESBETWEEN(Dates[Date],
MIN(Dates[Date]) ,MAX(Dates[Date])))),[ABCD] >= 500))
 
I think part of my problem is that the year in my data table is in text format.  But I'd just like to say:
 
IF 'TKD'[year] is the current year then X measure, else use the formula above.  The end result will look like this:
 

Hope someone is able to follow what I'm saying.  Any help would be greatly appreciated!

 

Thanks,

Jordan

 
Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous try this

     

    hhh = IF( MAX ( TKD[year]) = FORMAT (YEAR( TODAY() ), "General Number" )

or

hhh = IF( VALUE ( MAX ( TKD[year]) ) = YEAR( TODAY() ),

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Awesome - the first option worked.  Thank you!!! parry2k 

       

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous simply use following dax

     

    Measure = 
IF ( MAX ( Calendar[Year] ) = YEAR ( TODAY() ),
   [Current Year Fomula],
   [Past Year formula]
)

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks parry2k .  I think you have me really close but I'm getting the error that I can't compare text with integers.  I believe it's because my 'TKD'[year] is in a text formate.  Do you know how I can fix the formula maybe with a format or value function?

       

      hhh = IF( MAX ( TKD[year]) = YEAR( TODAY() ),
      [YTD Att>500],
      COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(TKD[timekeeper_id]),
      DATESBETWEEN(Dates[Date],MIN(Dates[Date]) ,MAX(Dates[Date]))),
      [timekeeper_id],"ABCD",CALCULATE(SUM(TKD[Atty Hours]),DATESBETWEEN(Dates[Date],
      MIN(Dates[Date]) ,MAX(Dates[Date])))),[ABCD] >= 500)) )
       
      Thank you!!!
       
      Jordan