Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help determing week when changing from yes to no

 

Hi everyone! 

 

i'm currently trying to determine when a salmon farming site becomes actve based on when they are reporting sea lice numbers. 

I have an excel spread sheet which can be downloaded from the autorities (https://www.barentswatch.no/api/v1/geodata/download/fishhealth?filetype=xlsx&reporttype=lice&localityno=undefined&fromyear=2015&toyear=2019&fromweek=1&toweek=52)

 

Based on this i want to get power bi to report in a calculated collumn which week in a year the site became active again. preferably using the global week approach which i am using for all my sheets to identify weeknumber.

 

Globalweek = 
VAR MaxWeeks = SUMMARIZE(ALL('Lakselus per fisk (2)');'Lakselus per fisk (2)'[År];"MaxWeek";MAX('Lakselus per fisk (2)'[Uke]))
VAR MyYear = [År]
VAR MyStart = SUMX(FILTER(MaxWeeks;[år]<MyYear);[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('Lakselus per fisk (2)'[År];1);ALL('Lakselus per fisk (2)'))
VAR myNum = IF(MyYear=firstYear;[Uke];MyStart+[Uke])
RETURN myNum

 

First some background info: 

Every week farmers report if their site is active (yes/no). If Yes then there will be additional information, all of this is presented on a row. 

After a production cycle, the site can be inactive for between 3 months to 2 years depending on the enviromental situation. 

Therefore, the expression need to take this into concideration. One production cycle usualy lasts between 16-25 months. 

Needs from the function: 

I need power BI to report the Global week when [Brakklagt] (active) changes from "Ja" (Yes) to "Nei" (No), based on [lokalitetsnummer] and [lokalitetsnavn] 

 

As there is data from many production cycles and years involved there needs to be a way of seperating this.

for example it could be a variable that if there is more than 4 globalweeks since the last time [brakklagt] was "Nei" then next time it changes from "Ja" to "Nei" it is a new production cycle.

 

Got a bit complicated there, please ask  if there is any needed clarification. 

All help is greatly aprreciated!

 

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous,

     

    You may modify your measure like below and try again.

    Globalweek =
VAR MaxWeeks =
    SUMMARIZE (
        ALL ( 'Lakselus per fisk' ),
        'Lakselus per fisk'[År],
        "MaxWeek", MAX ( 'Lakselus per fisk'[Uke] )
    )
VAR MyYear = [År]
VAR Start_Week =
    CALCULATE (
        MIN ( 'Lakselus per fisk'[Uke] ),
        FILTER ( 'Lakselus per fisk', 'Lakselus per fisk'[Brakklagt] = "Ja" )
    )
VAR End_Week =
    CALCULATE (
        MAX ( 'Lakselus per fisk'[Uke] ),
        FILTER ( 'Lakselus per fisk', 'Lakselus per fisk'[Brakklagt] = "Nei" )
    )
VAR MyStart =
    SUMX (
        FILTER ( MaxWeeks, [år] < MyYear && [Uke] >= Start_Week && [Uke] <= End_Week ),
        [MaxWeek]
    )
VAR firstYear =
    CALCULATE (
        FIRSTNONBLANK ( 'Lakselus per fisk'[År], 1 ),
        ALL ( 'Lakselus per fisk' )
    )
VAR myNum =
    IF ( MyYear = firstYear, [Uke], MyStart + [Uke] )
RETURN
    myNum

     

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Jimmy, and thank you for your suggestion. 

       

      I tried your approach, but it did not provide the desired result. I might have been a bit unclear in the text. I copied in the Globalweek code so that it would be easy for you to create a powerbi sheet similar to mine. - Sorry for that. 

       

      However, I like your approach and think it's not far of. Right now the calculation i did with the meassure just provides the Global week. 

       

      For the next few explaning pictures i've sorted by [lokalitetsnavn] to "Aldeøyna" to be able to show you what i need. 

       

      As you can see the column [uke for utsett], which contains your meassure is giving the identical result of the globalweek column. 

       

      What i want the collumn to describe is however which Globalweek there was a change in the [brakklagt] column from "Ja" to "Nei". (as seen in column 2 from the right). To take into concideration that there also has been multiple productioncycles at a spesific site we need to add a Maxweekgap variable, that says that if its more than 4 global weeks since the last the last change in "Ja" to "Nei" it is a new cycle. If you sort by the site i descirbed above, i think you will see what i mean. 

       

      We also need to add some more filters f. eks [lokalitetsnummer] which is a uniqe ID per site. 

       

      In advance, thank you alot for taking interest and trying to help out!