0

I'm trying to find a dynamic way to calculate the number of fiscal weeks left in the quarter (starting 10/1) in Power BI.

In my Dates reference table (which is a typical date dimension table with a value for every day). I've got calculated columns for Fiscal Year, Fiscal Quarter, and Fiscal Week, which populate correctly. Max weeks per quarter shifts depending on whether or not there's 13 or 14 weeks in the first quarter, so I basically need an equivalent of a MAXIFS function in excel to find the max weeks in each Fiscal Quarter based on Fiscal Year.

For reference, this is the DAX code I used with static week numbers by quarter:

Weeks Left = SWITCH(TRUE(),
[current quarter]=1,14-[fiscal week],
[current quarter]=2,26-[fiscal week],
[current quarter]=3,39-[fiscal week],
[current quarter]=4,52-[fiscal week])

So I'm looking to either create another calculated column to find the max in the current quarter/FY to replace the static week values, or placing the maxif-equivalent function in the above. Thanks in advance!

sockpuppetmonkey
  • 51
  • 7

1 Answers1

1

I actually ended up finding my own answer thanks to this post

So I have a calculated column in my dates table with the following, effectively eliminating the need for the SWITCH function I used in my question:

WeeksinQ = calculate(max([fiscal week]),filter(dates,[fy quarter]=EARLIER([fy quarter])))

...and am also using a separate "CurrentDate" table with a calculated column for weeks left:

WeeksinQuarter = related(dates[WeeksinQ])
sockpuppetmonkey
  • 51
  • 7