Reply
Regular Visitor
Posts: 29
Registered: ‎11-30-2018
Accepted Solution

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

 


Accepted Solutions
AlB Super Contributor
Super Contributor
Posts: 1,188
Registered: ‎11-12-2018

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

 

View solution in original post


All Replies
AlB Super Contributor
Super Contributor
Posts: 1,188
Registered: ‎11-12-2018

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.

Highlighted
Regular Visitor
Posts: 29
Registered: ‎11-30-2018

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

AlB Super Contributor
Super Contributor
Posts: 1,188
Registered: ‎11-12-2018

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

 

Regular Visitor
Posts: 29
Registered: ‎11-30-2018

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. 

AlB Super Contributor
Super Contributor
Posts: 1,188
Registered: ‎11-12-2018

Re: count weeks since last input

@Frivik

 

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