topic Re: Calculate First Day of Fiscal Week, Month and Quarter based on date selected in DAX Commands and Tips

Calculate First Day of Fiscal Week, Month and Quarter based on date selected

Ackbar-Learner — Fri, 14 Apr 2023 11:16:26 GMT

Hi I have a date range selector as below.

Using below dax, I get the "ToDate"

02DateTo = max(BudgetCalendar[Date])

Now I wish to retrieve the first date of that fiscal week, month and quarter of that "ToDate"

For example, if 04-Oct-23 is selected as ToDate, the corresponding fiscal week will be 40, fiscal month 10 and fiscal quarter 4.

I need a dax measure that will tell me the start date of week 40, fiscal month 10 and fiscal quarter 4. Please note that fiscal week, month and quarter might not necessarily start on the 1st of each month.

Thanks in advance

Re: Calculate First Day of Fiscal Week, Month and Quarter based on date selected

johnt75 — Fri, 14 Apr 2023 12:52:52 GMT

You can use

Start Fiscal Quarter = CALCULATE ( MIN ( 'Budget Calendar'[Date] ), ALL ( 'Budget Calendar' ), VALUES ( 'Budget Calendar'[Fiscal Year Quarter] ) )

Re: Calculate First Day of Fiscal Week, Month and Quarter based on date selected

Ackbar-Learner — Fri, 14 Apr 2023 13:10:45 GMT

Not working as intended. It is returning the first date in my budget calendar 😞. Remember there is a date filter already on the page!

Re: Calculate First Day of Fiscal Week, Month and Quarter based on date selected

johnt75 — Fri, 14 Apr 2023 13:24:02 GMT

Try

First day fiscal quarter = VAR CurrentQuarter = LOOKUPVALUE ( 'Budget calendar'[Fiscal year quarter], 'Budget calendar'[Date], [02DateTo] ) RETURN CALCULATE ( MIN ( 'Budget Calendar'[Date] ), REMOVEFILTERS ( 'Budget Calendar' ), 'Budget Calendar'[Fiscal year quarter] = CurrentQuarter )

Re: Calculate First Day of Fiscal Week, Month and Quarter based on date selected

Ackbar-Learner — Fri, 14 Apr 2023 13:36:30 GMT

This works fine 🙂 thanks!