Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Rolling 12 months Calculation with Ifs condition


Hello, I have a visual where I would like to display only data for the last 12 complete months with Some condition. but i have two separate data file, FY2324 & FY2425, Here some customer done business in different months So i need to display Customer wise Achvement For example

 

 

i Need result like 

 

Can Someone help

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Thanks @amitchandak  for the quick reply. Allow me to offer some other insights:

     

    First, replace the null values in columns [FY2324.Achv] and [FY2425.Achv] with 0.

    Then you can create a custom column.

    if [FY2425.Achv]>100000 then "GREEN+"
else if [FY2425.Achv]>=170 or [FY2324.Achv]>=170 then "GREEN"
else if [FY2425.Achv]<=49999 or [FY2324.Achv]<=49999 then "AMBER"
else if [FY2425.Achv]<=0 or [FY2324.Achv]<=0 then "RED"
else null

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Amit

      help me  measure how i use filter measure only 12 month data its  shoud calcalute only 12 month peried not overall,  This Function i am using in Transform New column, SO its taking complate data

       

      =IFS(OR(FY2425>=100000),"GREEN+",OR(FY2425>=50000,FY2324>=50000),"GREEN",OR(FY2425<=49999,FY2324<=49999),"AMBER",OR(FY2425<=0,FY2324<=0),"RED")

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you for input Amit, 
    Actally i have merged both table but i have to check last 12 month what i have Achved So next

    i need use =IFS(OR(FY2425>=100000),"GREEN+",OR(FY2425>=50000,FY2324>=50000),"GREEN",OR(FY2425<=49999,FY2324<=49999),"AMBER",OR(FY2425<=0,FY2324<=0),"RED") then result should be below image

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Thanks @amitchandak  for the quick reply. Allow me to offer some other insights:

     

    First, replace the null values in columns [FY2324.Achv] and [FY2425.Achv] with 0.

    Then you can create a custom column.

    if [FY2425.Achv]>100000 then "GREEN+"
else if [FY2425.Achv]>=170 or [FY2324.Achv]>=170 then "GREEN"
else if [FY2425.Achv]<=49999 or [FY2324.Achv]<=49999 then "AMBER"
else if [FY2425.Achv]<=0 or [FY2324.Achv]<=0 then "RED"
else null

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.