cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frivik Regular Visitor
Regular Visitor

count weeks since last input

Hi everyone, need some help with this one. 

 

I have a column where the globalweek is given if there is a change in another collumn.

These are reoccuring events continiuing through the data. What i need is a meassure counting the amount of blanks between the values( I.E the amount of weeks since the last occurence). The count needs to start at MIN. Could it be a possibility to start a count from min, and everytime there is a value in the [Uke start for rapporting] column it restarts the count? 

 

Any ideas? 

Thank you

Capture.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: count weeks since last input

Hey @Frivik

How you doing? Sorry, I couldn't reply earlier.

I had a look at your file and based on the [uke start for rapportering] I've put together the following for the new column. I suspect it's not the most efficient way, actually a bit slow but it seems to work. If someone has a faster version I'd be most interested in seeing it. 

Let me know if this is actually what you were looking for as I'm not sure I've understood correctly.

Cheers

 

CountWithinCycle = 
VAR _Min = 1 //Set here the initial value for the count 
VAR _CurrentGWeek = 'Lakselus per fisk'[Globalweek]
VAR _LatestCycleChange =
    CALCULATE (
        LASTNONBLANK ( 'Lakselus per fisk'[Uke start for rapportering]; 1 );
        'Lakselus per fisk'[Globalweek] <= _CurrentGWeek;
        ALLEXCEPT ( 'Lakselus per fisk'; 'Lakselus per fisk'[Lokalitetsnummer] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Lakselus per fisk' );
        'Lakselus per fisk'[Globalweek] >= _LatestCycleChange;
        'Lakselus per fisk'[Globalweek] <= _CurrentGWeek;
        ALLEXCEPT ( 'Lakselus per fisk'; 'Lakselus per fisk'[Lokalitetsnummer] )
    )
        + ( _Min - 1 )

 

Code formatted with   www.daxformatter.com

 

5 REPLIES 5
Super User
Super User

Re: count weeks since last input

Hi @Frivik

Can you show the (structure of) the full table? There should be a column that we'll use to "sort" the table or as reference.  Otherwise the number of blanks between the two occurrences would have no meaning since the order in which rows are shown is irrelevant.

If you can share the pbix, as you know, even better. Beware of confidential data.

Frivik Regular Visitor
Regular Visitor

Re: count weeks since last input

Hi again AIB, thank you for taking interest. I've attached a cleaned version of my .pbix for you to review. 
http://www.filedropper.com/communityfile

 

I would suggest that you Select [Lokalitetsnavn] "Aldeøyna" to see if you get it right. It contains data from several production cycles which are seperated by blanks in the [uke start for rapportering] column if you sort by [Globalweek]. As there is many different sites in this sheet you can use [lokalitetsnummer] as a uniqe site ID. 

 

The ideal solutuon would be a new calculated collumn that identifies a new production cycle. Which can be defined as when [Braklagt] changes from "Ja" to "Nei", and last until it again changes back to "Ja" seperated by site [Lokalitetsnummer]. I've made it halfway there through modifying a measure you previously gave me to be able to identify which week it changes. 

 

Please take a look. 

 

Cheers

Super User
Super User

Re: count weeks since last input

Hey @Frivik

How you doing? Sorry, I couldn't reply earlier.

I had a look at your file and based on the [uke start for rapportering] I've put together the following for the new column. I suspect it's not the most efficient way, actually a bit slow but it seems to work. If someone has a faster version I'd be most interested in seeing it. 

Let me know if this is actually what you were looking for as I'm not sure I've understood correctly.

Cheers

 

CountWithinCycle = 
VAR _Min = 1 //Set here the initial value for the count 
VAR _CurrentGWeek = 'Lakselus per fisk'[Globalweek]
VAR _LatestCycleChange =
    CALCULATE (
        LASTNONBLANK ( 'Lakselus per fisk'[Uke start for rapportering]; 1 );
        'Lakselus per fisk'[Globalweek] <= _CurrentGWeek;
        ALLEXCEPT ( 'Lakselus per fisk'; 'Lakselus per fisk'[Lokalitetsnummer] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Lakselus per fisk' );
        'Lakselus per fisk'[Globalweek] >= _LatestCycleChange;
        'Lakselus per fisk'[Globalweek] <= _CurrentGWeek;
        ALLEXCEPT ( 'Lakselus per fisk'; 'Lakselus per fisk'[Lokalitetsnummer] )
    )
        + ( _Min - 1 )

 

Code formatted with   www.daxformatter.com

 

Frivik Regular Visitor
Regular Visitor

Re: count weeks since last input

Fantastic work AIB! Thank you very much, i've been pondering on this problem for weeks and you have just unlocked a whole host of new opportunities for me.

 

Have a beer and enjoy your Saturday, 

 

Cheers. 

Highlighted
Super User
Super User

Re: count weeks since last input

@Frivik

 

Cool. I'll try to find some Norwegian beer Smiley Wink You enjoy it too.