- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# count weeks since last input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-17-2019 03:16 AM

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

Solved! Go to Solution.

Accepted Solutions

## Re: count weeks since last input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 01:34 PM

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 )

All Replies

## Re: count weeks since last input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-17-2019 05:27 AM

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.

## Re: count weeks since last input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-17-2019 05:55 AM

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

## Re: count weeks since last input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 01:34 PM

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 )

## Re: count weeks since last input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-19-2019 02:12 AM

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.

## Re: count weeks since last input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-19-2019 02:54 AM