Forum Discussion

anil_power's avatar
anil_power
Regular Visitor
8 years ago

last 3 Days Variance per ID

Hi all,          I have a dataset of ID, Date, and value. I am trying to calculate the last 3 values of ID based on the last 3 dates. So once we get the last 3 values based on these trying to write ...
Need Help
Show and Tell
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

I can't really fathom your logic as to how you are getting red, yellow, green, I'm not comprehending your explanation of it. But it is going to be something like:

 

Last 3 = 
VAR __tmpTable = SUMMARIZE(FILTER(ALL('Sample'),[ID]=EARLIER([ID]) && [Date]<=EARLIER([Date]) && [Date]>=EARLIER([Date])-2),'Sample'[Bucket])
VAR __rows = COUNTROWS(__tmpTable)
RETURN
IF(__rows = 1,MAXX(__tmpTable,[Bucket]),IF("Yellow" IN __tmpTable, "Yellow", IF("Green" IN __tmpTable, "Green", "Red")))

 

 

anil_power's avatar
anil_power
Regular Visitor
8 years ago

Greg,

         Can you please attach pbix file so it would be helpful. 

    • anil_power's avatar
      anil_power
      Regular Visitor
      8 years ago

      Thanks Greg, your reply was really helpful.

       

      The Bucket column in data I am calculated using below formula. 

       

      Bucket = Switch ( True(),

      And( [Avg Discount %]>=-10 ,[Avg Discount %] < [TMax 5% Var]) , "Green",
      And( [Avg Discount %]>=[TMax 5% Var],[Avg Discount %] < [TMax 15% Var]) , "Yellow",
      And( [Avg Discount %] >= [TMax 15% Var],[Avg Discount %] < 500), "Red"

      )

       

      and I have used the calculated column formula given by you, but I am facing dependency error while applying the formula

       

       

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        8 years ago

        Hi anil_power,

         

        Please refer to this blog to avoid A circular Dependency was detected" error.

         

        Best Regards，

        Cherry