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.
Hi guys,
I have a custom Date Table that takes into account my Fiscal Year ( Feb - Starting FY month). It also has a fiscal week calculation.
But I need to take it a step further, and essentially make sure each week has full length period (Monday -Sunday).
So for example, where a fiscal week begins on a day that does not equal "Monday" such as February 1st 2022 (Tuesday) it needs to be pushed to the next week.
So for cases such as above, where the full week is not recognized from Monday To Sunday - it needs to be rolled up into the next week until its showing only 1 week at a time.
Is this possible in Power Query ?
@cdawidow2 , check the dax solution
FY Year = if(month([Date]) <2, YEAR([Date]) -1, YEAR([Date]) )
FY Start= if(month([Date]) <2, Date(YEAR([Date]) -1,2,1) , date(YEAR([Date]),2,1) )
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
FY Week Start Year = minx(filter('Date',[FY Year] =earlier([FY Year]) && [Week Start date] > [FY Start] ),[Week Start date])
FY Week = QUOTIENT(DATEDIFF([FY Week Start Year ],'Date'[Start of Week]),[Date],DAY),7)+1
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 |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |