Help Calculating a Season to Date Plan number - With a custom calendar

[jnunn]

Hello

I am trying to calculate my STD plan number while using as custom 4-5-4 fiscal calendar.  

The formula I tried to use is below and it gave me (Blank) as my answer.

Note: That the [Total Plan] is from another dataset that is linked to the 4-5-4 Calendar by the Fiscal Week Number.

Season To Date Plan =

VAR CurrFiscalWk = SELECTEDVALUE('4-5-4 Calendar'[FiscalWeekNumber])

VAR CurrFiscalYR = SELECTEDVALUE('4-5-4 Calendar'[FiscalYear])

Return

CALCULATE([Total Plan],

FILTER (ALL('4-5-4 Calendar'),

'4-5-4 Calendar'[CalendarYear]= CurrFiscalYR &&

'4-5-4 Calendar'[FiscalWeekNumber] <= CurrFiscalWk))

Any help would be greatly appericated.

Thank you for looking.

Jeff

[Stachu]

What is the syntax for the [Total Plan]?

Also, can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[jnunn]

My total plan syntax is: Total Plan = sum('Plan Info'[Plan])

My plan table is as below. There are about 5 different departments with a plan in each for all 26 weeks of Spring.

Department	Week Number	Plan
366	1	312011
366	2	428922
358	1	312011
358	2	4289225

My 4-5-4 fiscal calendar is pretty big and wont fit well in here.  I can send you the Advanced Editor copy if you would like. 

Thanks for taking the time to look into this.  Jeff

[Stachu]

Advanced Editor copy will be fine, please post it

EDIT how do you do the join? You filter both Year and Week Nr, but there cannot be join with the table that you posted that would make this work

Also you have

VAR CurrFiscalYR = SELECTEDVALUE('4-5-4 Calendar'[FiscalYear])

and 

'4-5-4 Calendar'[CalendarYear]= CurrFiscalYR &&

you pickup value from '4-5-4 Calendar'[FiscalYear] and use it in '4-5-4 Calendar'[CalendarYear], which I assume have different entries - maybe that's the main issue?

[jnunn]

Here is the Advanced Editor for my calendar.  

let
#"4-5-4 Calendar" = let
CreateCustomCalendar = (StartDate as date, FiscalYear as number, FiscalYearSequence as number) as table =>
let
InitialDates28 = Table.FromList(
List.Dates(StartDate,364, #duration(1,0,0,0)),
Splitter.SplitByNothing(),
type table [Date=Date.Type],
null,
ExtraValues.Ignore),


InitialDates35 = Table.FromList(
List.Dates(Date.AddDays(StartDate, -28), 364, #duration(1,0,0,0)),
Splitter.SplitByNothing(),
type table [Date=Date.Type],
null,
ExtraValues.Ignore),



// split dates into 3 tables
// one containing periods 1, 4, 7, 10, EACH OF 28 DAYS
// one containing periods 2,5,8,11 each of 35 days
// one containing periods 3,6,9,12 each of 28 days
Tbl281 = Table.AlternateRows(InitialDates28, 7*4, 7*5 + 7*4, 7*4),
Tbl35 = Table.AlternateRows(InitialDates35,0, 7*4 + 7*4, 7*5),
Tbl282 = Table.AlternateRows(InitialDates28, 0, 7*5 + 7*4, 7*4),

Tbl281AddIndex = Table.AddIndexColumn(Tbl281,"Indx",0,1),
Tbl281AddInt = Table.AddColumn(Tbl281AddIndex, "FiscalPeriodNumber", each Number.IntegerDivide([Indx],28) * 3 + 1, Int64.Type),


Tbl35AddIndex = Table.AddIndexColumn(Tbl35, "Indx", 0, 1),
Tbl35AddInt = Table.AddColumn(Tbl35AddIndex,"FiscalPeriodNumber", each Number.IntegerDivide([Indx],35) * 3 + 2, Int64.Type),

Tbl282AddIndex = Table.AddIndexColumn(Tbl282,"Indx",0,1),
Tbl282AddInt = Table.AddColumn(Tbl282AddIndex, "FiscalPeriodNumber", each Number.IntegerDivide([Indx],28) * 3 + 3, Int64.Type),



CombineTbl = Table.SelectColumns(Table.Sort(Table.Combine({Tbl281AddInt,Tbl35AddInt,Tbl282AddInt}), {"Date",Order.Ascending}),{"Date","FiscalPeriodNumber"}),

// add additional Fiscal info columns
AddPeriodLabel = Table.AddColumn(CombineTbl, "FiscalPeriodLabel", each "P" & Text.From([FiscalPeriodNumber]), type text),
AddFiscalPeriodSequence = Table.AddColumn(AddPeriodLabel, "FiscalPeriodNumberSequence", each [FiscalPeriodNumber] + (FiscalYearSequence - 1) * 12, Int64.Type),

AddFiscalQuarter = Table.AddColumn(AddFiscalPeriodSequence, "FiscalQuarterNumber", each Number.IntegerDivide([FiscalPeriodNumber]-1,3)+1, Int64.Type),
AddFiscalQuarterLabel = Table.AddColumn(AddFiscalQuarter, "FiscalQuarter", each "Q" & Text.From([FiscalQuarterNumber]), type text),
AddFiscalQuarterSequence = Table.AddColumn(AddFiscalQuarterLabel, "FiscalQuarterSequence", each [FiscalQuarterNumber] + (FiscalYearSequence - 1) * 4, Int64.Type),
AddFiscalSeason = Table.AddColumn(AddFiscalQuarterSequence, "FiscalSeasonNumber", each Number.IntegerDivide([FiscalPeriodNumber]-1,6)+1, Int64.Type),

AddFiscalYear = Table.AddColumn(AddFiscalSeason,"FiscalYear", each FiscalYear, Int64.Type),

AddDayNumberOfFiscalYear = Table.AddIndexColumn(AddFiscalYear,"DayNumberOfFiscalYear",1,1),
AddDayNumberOfFiscalYearSequence = Table.AddColumn(AddDayNumberOfFiscalYear, "DayNumberOfFiscalYearSequence", each [DayNumberOfFiscalYear] + (FiscalYearSequence - 1) * 364, Int64.Type),
AddFiscalWeekNum = Table.AddColumn(AddDayNumberOfFiscalYearSequence,"FiscalWeekNumber",each Number.IntegerDivide([DayNumberOfFiscalYear]-1,7)+1, Int64.Type),
AddFiscalWeekLabel = Table.AddColumn(AddFiscalWeekNum, "FiscalWeekLabel", each "Week " & Text.From([FiscalWeekNumber]), type text),
AddFiscalWeekNumSequence = Table.AddColumn(AddFiscalWeekLabel, "FiscalWeekNumberSequence", each [FiscalWeekNumber] + (FiscalYearSequence - 1) * 52, Int64.Type),

// add Calendar Info columns
AddCalendarWeek = Table.AddColumn(AddFiscalWeekNumSequence, "CalendarWeek", each Date.WeekOfYear([Date],Day.Sunday), Int64.Type),
AddCalendarWeekLabel = Table.AddColumn(AddCalendarWeek, "CalendarWeekLabel", each "Week " & Text.From([CalendarWeek]), type text),
AddMonthNumber = Table.AddColumn(AddCalendarWeekLabel, "MonthNumberOfCalendarYear", each Date.Month([Date]), Int64.Type),
AddMonthName = Table.AddColumn(AddMonthNumber, "MonthName", each Date.MonthName([Date]), type text),
AddWeekDayNumber = Table.AddColumn(AddMonthName, "DayNumberOfWeek", each Date.DayOfWeek([Date], Day.Monday) + 1, Int64.Type),
AddWeekDayName = Table.AddColumn(AddWeekDayNumber, "WeekdayName", each Date.ToText([Date],"dddd"), type text),
AddCalendarQuarter = Table.AddColumn(AddWeekDayName, "QuarterOfCalendarYear", each Date.QuarterOfYear([Date]), Int64.Type),
AddCalendarQuarterLabel = Table.AddColumn(AddCalendarQuarter, "QuarterLabelOfCalendarYear", each "Q" & Text.From([QuarterOfCalendarYear]), type text),
AddCalendarYear = Table.AddColumn(AddCalendarQuarterLabel, "CalendarYear", each Date.Year([Date]), Int64.Type),
AddPreviuosWeek = Table.AddColumn(AddCalendarYear, "Previous Week", each Date.IsInPreviousWeek([Date]))

in
AddPreviuosWeek,


//AddPreviuosWeek = Table.AddColumn(AddCalendarYear, "Previous Week", each Date.IsInPreviousWeek(Date.AddDays([Date],-0)), type text)

CreateWholeCalendar = (InitialDate as date, InitialFiscalYear as number, NumberOfCalendars as number) as table =>
Table.Combine(List.Accumulate({1..NumberOfCalendars},
{},
(state,current) => state & {CreateCustomCalendar(Date.AddDays(InitialDate, (current -1) * 364),
InitialFiscalYear + current - 1,
current)}
))


in
CreateWholeCalendar(#date(2020,2,2),2020,5),
#"Added Custom" = Table.AddColumn(#"4-5-4 Calendar", "Current Date", each DateTime.FixedLocalNow()),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Current Date", type date}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Year", each if [Current Date] >= [Date] then true else false),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Current Fiscal Day", each if [Current Date] = [Date] then [DayNumberOfFiscalYear] else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Current Fiscal Day", Int64.Type}})
in
#"Changed Type1"

[Stachu]

How do you join 'Plan Info' with '4-5-4 Calendar'? With the data you posted it's only possible on single week with Many:Many, which will not work. Are there more tables involved that you have not included? Or year specific columnin 'Plan Info'?

[jnunn]

I have them joined as a many to many.  I tried ading a third table that just had the week numbers but I could not get that to work either and I think it caused another issue.  I can add the other table back if needed.

Jeff

[Stachu]

OK, so if the relation is just on Week Nr, how is the Year supposed to work? it seems there is no year granularity in 'Plan Info'

Department	Week Number	Plan
366	1	312011
366	2	428922
358	1	312011
358	2	4289225

let's say you filter Year to 2020 and Week to 1 in the slicer from '4-5-4 Calendar' - what should be the output in this scenario?  

What should be the output if you filter 2021 and 1?

[jnunn]

I have been at this for a little bit and the formula above was based on something I read.  It may not be the correct way of doing what I need done. 

The goal is to not use filters, I want/need everything to be based off of the current date.  Based on the date I want to know what the Previous week number is, and what the season, month and year the previous week lives in.   

My ultimate goal is to the figure the cumulative plan numbers for previous week, season to date, year to date and eventually month to date. 

That all being said if I use today, March 27, as a example it look like this:

3/20 lives in Fiscal week 8

For the previous week I then need to see the plan for week #7, the for my season to date number it would be the sum of weeks 1 thru 7, and the month wpuld be sum of weeks 5 through 7. 

I have figured out a way to show the previous week in the calendar.  I just need some way to sum the 7 week of the season.  If this can be done in the calendar instead of DAX that will work too. I just thought DAX would be easier.

Thanks again for looking into my issue.  

Jeff

[Stachu]

So if I understand correctly you don't actually want to use slicers, just base the calculation on current date, correct?

I still don't undersantd how you manage multiple years though. Does the week counter reset with the new year? Say, if you have last week of 2020 and first week of 2021 - would their indexes be 53 and 1 or 53 and 54? And if that doesn't apply to you then why in the Calendar table are there multiple years?

[jnunn]

The current date would also dictate the year.  The week counter is reset with the year.  After week 53 we go back to week 1.  I will say that the plan will  probably not cross years so it may not be totally relative.  The reason I put multiple years in the calendar is beacuse I am building this for another user and I want it to work for them for multiple years, as they dont have the expetiece to fix it.. 

Jeff 

[Stachu]

how does current date dictate the year?

you posted this data:

Department	Week Number	Plan
366	1	312011
366	2	428922
358	1	312011
358	2	4289225

to which year does the first 1 belong, and to which year does the second 1 belong? Right now it could be any of the 5 years that you have in the calendar table. Right now the year is 2020, so we could assume that it's relating to 2020, but when the year changes to 2021 it would mean that the same record relates to 2021, which seems incorrect

[jnunn]

All of the data in the plan is fo 2020.  They will never be a time whan a plan would go across 2 fiscal years.  

Jeff