Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Users,
I am trying to create a customized Fiscal week column based on the Fiscal period starting from September - August and week starting on Monday.
My Problem is i am getting partial weeks and really want to get rid of it. As an example by using the below Dax I am getting, 01-Sept-2019 ( Sunday ) as week 1 and 02-Sept-2019 (Monday) as Week 2 , similarly 31-Aug-2020 as Week 54.
Need suggestions as to compute Dax such that we get only full weeks and only 52 weeks in total for year. Then 01-sept-2019 will have no week number and so would be the case with 31-Aug-2020.
Dax Formula used:
Thanks in advance for your help!!
Solved! Go to Solution.
Hi @Powereports ,
Try this:
Modified Fiscal Week =
VAR DaysofBlank =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
&& 'Calendar'[Fiscal WeekNum] = BLANK ()
)
)
VAR MaxWeek =
CALCULATE (
MAX ( 'Calendar'[Fiscal WeekNum] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
)
)
VAR DayofMaxWeek =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
&& 'Calendar'[Fiscal WeekNum] = MaxWeek
)
)
VAR Week =
IF (
[Fiscal WeekNum] = BLANK ()
|| [Fiscal WeekNum] = MaxWeek
&& DayofMaxWeek <> 7,
BLANK (),
[Fiscal WeekNum]
)
VAR ModifiedWeek =
SWITCH (
TRUE (),
MaxWeek >= 53, Week,
MaxWeek = 52
&& DayofMaxWeek >= 4, [Fiscal WeekNum],
MaxWeek <= 52
&& DayofMaxWeek < 4
&& DaysofBlank >= 4,
IF ( [Fiscal WeekNum] <> MaxWeek, Week + 1 )
)
RETURN
IF ( ModifiedWeek <> BLANK (), "Week " & ModifiedWeek )
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Hi @Powereports ,
Try this:
1. Create a 'Fiscal Year' column.
Fiscal Year =
VAR FiscalStartMonth = 9
RETURN
IF (
MONTH ( 'Calendar'[Date] ) < FiscalStartMonth,
"FY"
& YEAR ( 'Calendar'[Date] ) - 1,
"FY" & YEAR ( 'Calendar'[Date] )
)
2. Create a 'Fiscal WeekNum' column.
Fiscal WeekNum =
VAR WeekDay =
WEEKDAY ( [Date], 2 )
VAR FiscalStartMonth = 9
VAR FiscalmaxWeek = 52 --Calculation--
VAR FiscalFirstDay =
IF (
MONTH ( 'Calendar'[Date] ) < FiscalStartMonth,
DATE ( YEAR ( 'Calendar'[Date] ) - 1, FiscalStartMonth, 1 ),
DATE ( YEAR ( 'Calendar'[Date] ), FiscalStartMonth, 1 )
)
VAR FilteredTableCount =
COUNTROWS (
FILTER (
SELECTCOLUMNS (
GENERATESERIES ( FiscalFirstDay, 'Calendar'[Date] ),
"Dates", [Value]
),
WEEKDAY ( [Dates], 2 ) = 1
)
)
RETURN
FilteredTableCount
3. Create a 'Modified Fiscal Week' column.
Modified Fiscal Week =
VAR MaxWeek =
CALCULATE (
MAX ( 'Calendar'[Fiscal WeekNum] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
)
)
VAR DayofMaxWeek =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
&& 'Calendar'[Fiscal WeekNum] = MaxWeek
)
)
RETURN
IF (
[Fiscal WeekNum] = BLANK ()
|| [Fiscal WeekNum] = MaxWeek
&& DayofMaxWeek <> 7,
BLANK (),
"Week " & [Fiscal WeekNum]
)
BTW, .pbix file attached.
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Hi Icey,
Thanks for solution. Your solution did solve my problem to some extent, but i am trying to figure out if there is a way to get 52 weeks as below.
I understand that based on the the Modified Weeknumber logic, Dax gave blank values for Sep1,2020- sep6,2020 but at the same time it returned only 51 weeks for the Fiscal year 2020. So I was wondering if we could use a condition such that if the weeknum <52 and First blank week or last blank week (In this case sep1,2020 -sep6,2020) whichever week has more than 4 days in that week , assign it a weeknumber (In this case Sep01-06 can be week 1?
Thanks again!! Really appreciate your help.
Hi @Powereports ,
Try this:
Modified Fiscal Week =
VAR DaysofBlank =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
&& 'Calendar'[Fiscal WeekNum] = BLANK ()
)
)
VAR MaxWeek =
CALCULATE (
MAX ( 'Calendar'[Fiscal WeekNum] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
)
)
VAR DayofMaxWeek =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] )
&& 'Calendar'[Fiscal WeekNum] = MaxWeek
)
)
VAR Week =
IF (
[Fiscal WeekNum] = BLANK ()
|| [Fiscal WeekNum] = MaxWeek
&& DayofMaxWeek <> 7,
BLANK (),
[Fiscal WeekNum]
)
VAR ModifiedWeek =
SWITCH (
TRUE (),
MaxWeek >= 53, Week,
MaxWeek = 52
&& DayofMaxWeek >= 4, [Fiscal WeekNum],
MaxWeek <= 52
&& DayofMaxWeek < 4
&& DaysofBlank >= 4,
IF ( [Fiscal WeekNum] <> MaxWeek, Week + 1 )
)
RETURN
IF ( ModifiedWeek <> BLANK (), "Week " & ModifiedWeek )
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
5 | |
4 | |
2 | |
2 |
User | Count |
---|---|
13 | |
9 | |
7 | |
2 | |
2 |