fiscal week belonging to only one month

[Anonymous]

Hi All,

I have a calendar table where the fiscal year start at November 1st each year, so for the FY18 the Fiscal Week W-1 goes from 01/11/2018 to 7/11/2018 and so on.

Knowing this, it is normal that a Fiscal Week is shared by two months (the end of a month and the beginning of other). Like in the example below, W-14 is placed in both January and February.

I do not want this to happened, I would like to keep a week number under a one month only. So, in this example 01/02/2018 should be W-15 and not W-14.

Any idea? I would appreciate this.

Many thanks,

José

[Greg_Deckler]

Probably a solution but it would probably take just as much time to come up with it as it would to just manually type them in.

[Greg_Deckler]

Been looking at this in my spare time. Still working but here is the first part, identifying which weeks span months:

Switch = 
VAR __week = [Week]
VAR __table = SUMMARIZE(FILTER(ALL('Calendar'),[Week]=__week),[Week],[MonthName],"__count",COUNT([Date]))
RETURN IF(COUNTROWS(__table)>1,1,BLANK())

[Greg_Deckler]

And here is a bit of an improved one. 

Switch 2 = 
VAR __week = [Week]
VAR __table = SUMMARIZE(FILTER(ALL('Calendar'),[Week]=__week),[Week],[MonthName],"__count",COUNT([Date]))
VAR __count = COUNTROWS(__table)
VAR __min = IF(__count>1,MINX(FILTER(ALL('Calendar'),[Week]=__week),[Date]),BLANK())
RETURN
IF(ISBLANK(__min),BLANK(),IF([Date]=__min,1,BLANK()))

[Greg_Deckler]

OK, sorry, this is probably the one that you want to use:

Switch 3 = 
VAR __week = [Week]
VAR __table = SUMMARIZE(FILTER(ALL('Calendar'),[Week]=__week),[Week],[Month],"__count",COUNT([Date]))
VAR __count = COUNTROWS(__table)
VAR __max = MAXX(__table,[Month])
VAR __min = IF(__count>1,MINX(FILTER(ALL('Calendar'),[Week]=__week && [Month]=__max),[Date]),BLANK())
RETURN
IF(ISBLANK(__min),BLANK(),IF([Date]=__min,1,BLANK()))

[Greg_Deckler · Accepted solution]

OK, I got it.

First, slight adjustment to Switch 3, I was returning 1 for the first day of the month when it switches but that wasn't quite right, or I guess I could have used a <= in my other measure, either way:

Switch 3 = 
VAR __week = [Week]
VAR __table = SUMMARIZE(FILTER(ALL('Calendar'),[Week]=__week),[Week],[Month],"__count",COUNT([Date]))
VAR __count = COUNTROWS(__table)
VAR __min = MINX(__table,[Month])
VAR __max = IF(__count>1,MAXX(FILTER(ALL('Calendar'),[Week]=__week && [Month]=__min),[Date]),BLANK())
RETURN
IF(ISBLANK(__max),BLANK(),IF([Date]=__max,1,BLANK()))

Then this column uses the Switch 3 column to adjust the week appropriately:

Expected FW = 
VAR __date = [Date]
VAR __adjustment = SUMX(FILTER(ALL('Calendar'),[Date]<__date),[Switch 3])
RETURN [Week]+__adjustment