topic Re: Fiscal Calendar with Fortnights in DAX Commands and Tips

Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 06:37:20 GMT

I have created a Fiscal Calendar, but I need to add in Fortnights. 1/7/2023 - 14/7/2023 as FN01 etc for 2023 Fiscal Year.

How to I do this?

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 06:39:25 GMT

In Power BI, you can create a Fiscal Calendar with Fortnights by following these general steps. Keep in mind that the specifics might vary based on your exact requirements and the structure of your data. I'll provide a high-level guide:

Create a Date Table:
- In Power BI, it's common to create a separate Date table that includes all the dates you need for your analysis.
Add a Fiscal Year Column:
- Create a column that assigns each date to a fiscal year. This could involve mapping each date to the corresponding fiscal year based on your organization's fiscal calendar.
Calculate Fortnights:
- You can create a new column to calculate the fortnight for each date. For example, you can use a formula like this:

Fortnight = CEILING(MINX(FILTER('DateTable', 'DateTable'[Date] <= EARLIER('DateTable'[Date])), 'DateTable'[Date]),14)/14

- This formula uses the CEILING function to round up the date to the nearest multiple of 14, effectively grouping dates into fortnights.
Combine Fiscal Year and Fortnight:
- Create a new column that combines the fiscal year and fortnight. For example:

FiscalYearFortnight = 'DateTable'[FiscalYear] & " FN" & FORMAT('DateTable'[Fortnight], "00")

Use the New Column in Visuals:
- Now, you can use the FiscalYearFortnight column in your visuals to analyze data by fiscal year and fortnight.

Remember that the exact implementation might depend on your specific fiscal calendar and the structure of your existing data. Adjust the formulas and column names according to your needs.

If you encounter any issues or have specific requirements, feel free to provide more details for further assistance.

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 07:23:01 GMT

Everything went well until the fortnight Number:

FORTNIGHT NUM = CEILING(MINX(FILTER('Date Table','Date Table'[Date] <= EARLIER('Date Table'[Date])),'Date Table'[Date]), 14)/14

then:

FISCAL YEAR FN = 'Date Table'[FISC YEAR] & " FN" & FORMAT('Date Table'[FORTNIGHT NUM], "01")

Column is:

What did I do wrong??

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 07:48:05 GMT

It seems like the issue might be related to how the fortnight number is calculated. The formula you provided uses the MINX and FILTER functions to find the minimum date up to the current date and then calculates the fortnight number using the CEILING function.

However, there's a simpler way to calculate the fortnight number without using MINX and FILTER. You can use the ROUNDUP function directly on the day of the year to get the fortnight number. Here's an updated formula:

FORTNIGHT NUM = ROUNDUP(DAY('Date Table'[Date])/14, 0)

And then, for your 'FISCAL YEAR FN' column:

FISCAL YEAR FN = 'Date Table'[FISC YEAR] & " FN" & FORMAT('Date Table'[FORTNIGHT NUM], "00")

This formula divides the day of the year by 14 and rounds up to the nearest whole number, giving you the fortnight number. The FORMAT function is then used to ensure that the fortnight number is displayed with leading zeros.

Try using this formula and see if it resolves the issue you're facing. If you encounter any further problems, please provide more details on the specific error or unexpected result you're getting.

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 07:54:13 GMT

Works better - but it is doing FN01 FN02 FN03 (July) then August comes and it is going FN01 FN02 FN03 again - almost there - so close!!!

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 08:01:11 GMT

I think it is with this formula FORTNIGHT NUM = ROUNDUP(DAY('Date Table'[Date])/14, 0) as it is in that column that the error is - on 31/7 it reverts from 3 back to 1 on 1/8

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 08:04:20 GMT

It seems like the issue is related to the transition between fiscal years. To address this, you can modify the FortnightNum calculation to consider the fiscal year transition. Here's an updated version:

FortnightNum =
VAR StartDate = DATE(YEAR('Date Table'[Date]), 1, 1)
VAR DaysFromStart = 'Date Table'[Date] - StartDate
VAR FortnightOffset = IF(DaysFromStart >= 0, 0, 1)
RETURN
INT((DAY('Date Table'[Date]) - 1 + FortnightOffset) / 14) + 1

This modification introduces the FortnightOffset variable, which adjusts the calculation based on whether the date is in the current fiscal year or the next fiscal year. This should prevent the FortnightNum from resetting to FN01 in August.

Update the 'FiscalYearFN' formula accordingly:

FiscalYearFN = 'Date Table'[FISC YEAR] & " FN" & FORMAT('Date Table'[FortnightNum], "00")

Make sure to replace 'Date Table' with the actual name of your date table.

This modification ensures that the fortnight number continues to increment throughout the fiscal year without resetting when transitioning to a new calendar year.

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 08:05:16 GMT

I see, the issue seems to be related to the way the fortnight number is calculated, especially when transitioning between months. Let's adjust the FortnightNum calculation to handle this scenario better:

This modification incorporates the FortnightOffset variable to adjust for the transition between fiscal years. It rounds up based on the number of days from the start of the fiscal year.

Update the 'FiscalYearFN' formula accordingly:

FiscalYearFN = 'Date Table'[FISC YEAR] & " FN" & FORMAT('Date Table'[FortnightNum], "00")

Please replace 'Date Table' with the actual name of your date table.

This adjustment should help in maintaining the continuity of fortnight numbers across the fiscal year transition.

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 08:28:25 GMT

OK it is now working it - THANKYOU!!!!! One last question - my fortnights start on a Saturday - any ideas? Thankyou so much - really stumped with this one

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 08:33:54 GMT

I'm glad to hear that it's working! If your fortnights start on a Saturday and you want to align your fortnight calculation with that, you can adjust the formula accordingly. Assuming that your fiscal year always starts on July 1, and the first day of the fiscal year is a Saturday, you can modify the formula like this:

FORTNIGHT NUM =
VAR FiscalYearStart = DATE(YEAR('Date Table'[Date]), 7, 1)
VAR DaysSinceStart = 'Date Table'[Date] - FiscalYearStart
VAR DaysOffset = MOD(DaysSinceStart, 7) // Calculate the number of days since the start of the fiscal year and find the offset from the first Saturday
RETURN
ROUNDUP((DaysSinceStart - DaysOffset + 1) / 14, 0)

This adjustment uses the MOD function to find the offset from the first Saturday in the fiscal year. This way, your fortnight count will start from the first Saturday and continue every 14 days.

Please replace 'Date Table' with your actual table name if it's different. Adjust the fiscal year start date in the FiscalYearStart variable if your fiscal year starts on a different date.

This modification should align your fortnight calculation with Saturdays at the start of each fortnight.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 08:37:25 GMT

OK - got excited too soon .......... worked perfectly, except 01/01/2024 now becomes FN -13 and it works backward 😞

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 08:41:38 GMT

I see. It seems that the current formula is not handling the negative values for fortnights as expected. Let's adjust the formula to ensure it works correctly for the entire fiscal year. Here's an updated version:

FORTNIGHT NUM =
VAR FiscalYearStart = CALCULATE(MIN('Date Table'[Date]), ALL('Date Table'))
VAR DaysSinceStart = DATEDIFF(FiscalYearStart, 'Date Table'[Date], DAY)
VAR FortnightNumber = CEILING((DaysSinceStart + WEEKDAY(FiscalYearStart) - 1) / 14, 1)
RETURN
IF(FortnightNumber <= 0, FortnightNumber + CEILING(DAY(FiscalYearStart) / 14, 1), FortnightNumber)

This modification includes an IF statement to handle cases where the calculated Fortnight Number is less than or equal to 0. In such cases, it adjusts the Fortnight Number to ensure it starts counting from the first fortnight.

Please replace 'Date Table'[Date] with your actual date column reference.

Give this adjustment a try, and it should address the issue of negative Fortnight Numbers. Let me know if you encounter any further issues or if you have additional questions!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 08:46:43 GMT

Thankyou - so the Fortnight Num is 1 from 1/7/2023 - 9/7/23 (whereas it should be to 14/7/23 - must be something in this formula as it was correct last step

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 09:20:37 GMT

Let's refine the formula to ensure that the fortnight numbering aligns correctly with your requirements. Specifically, the issue seems to be related to how the adjustment is handled for the start date of the fiscal year.

FORTNIGHT NUM =
VAR FiscalYearStart = DATE(YEAR('Date Table'[Date]), 7, 1)
VAR DaysElapsed = 'Date Table'[Date] - FiscalYearStart
VAR DaysToAdd = MOD(7 - WEEKDAY(FiscalYearStart, 2) + 1, 7) // Days to add to align with the start day

// Ensure the start date is treated as FN01
VAR AdjustedDaysElapsed = IF(DaysElapsed < 0, 0, DaysElapsed)

RETURN
ROUNDUP((AdjustedDaysElapsed + DaysToAdd) / 14, 0)

This formula calculates the adjusted days elapsed, ensuring that the start date of the fiscal year is considered FN01. Please replace your existing FORTNIGHT NUM column with this updated formula and test it across various dates, including the start date of the fiscal year.

I appreciate your patience, and I hope this refinement resolves the issue. If you encounter any further challenges or have additional questions, feel free to let me know!

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 09:25:43 GMT

Almost - I just had to change the Fiscal Year Start from , 2 to ,1) BUT when i gets to 01/01/2024 it goes to FN00 😞

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 09:42:22 GMT

I think this will solve your issue:

Fortnight = 
VAR __BaseDate = DATE (2019,1,1)
VAR __Days = ([Value]. [Date] - __BaseDate) * 1
RETURN
    "FN" & 
    FORMAT (
        INT ( __Days / 14 ) + 1,
        "00"
    )

This code will group dates into fortnights starting from January 1, 2019. You can modify the code to suit your specific requirements.

To add fortnights to your Fiscal Calendar in Power BI, you can create a calculated column using the above DAX code. You can also create a custom fiscal calendar in Power BI by following the steps outlined in this tutorial by Brunner BI.

I hope this helps!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 10:35:48 GMT

Sorry confused as to the [value] - what would that be referring to

Re: Fiscal Calendar with Fortnights

123abc — Wed, 29 Nov 2023 10:43:28 GMT

I apologize for the confusion. The [value] in the formula is a placeholder for the column name that contains the dates you want to use to calculate the fortnight number. You should replace [value] with the actual name of the column.

For example, if your date column is named Date, the formula would be:

FORTNIGHT NUM = 
VAR __BaseDate = DATE (2023, 1, 6) // January 6, 2023 is a Saturday
VAR __Days = ([Date] - __BaseDate) * 1
VAR __Month = MONTH([Date])
VAR __DayOfWeek = WEEKDAY([Date], 2) // 1 is Monday, 2 is Tuesday, ..., 7 is Sunday
VAR __Offset = IF(__DayOfWeek >= 6, 1, 0)
RETURN
    "FN" & FORMAT(__Month * 2 - __Offset + CEILING((__Days - __Offset) / 14, 1), "00")

I hope this clears things up. Let me know if you have any other questions.

Re: Fiscal Calendar with Fortnights

CharnanK — Wed, 29 Nov 2023 11:17:35 GMT

I worked it - changed your original measure - and it's perfect - thankyou for your help

Final measure:

FortnightNum =

VAR StartDate = DATE(2023, 7, 1) - WEEKDAY(DATE(2023,7,1),2) +5

VAR CurrentDate = 'date table'[Date]

VAR DaysElapsed = CurrentDate - StartDate

VAR FortnightNum = IF(DaysElapsed >= 364, 1, ROUNDUP(DaysElapsed / 14, 0))

RETURN

"FN" & FORMAT(FortnightNum, "00")

This now continues right through and restarts for the next fiscal year as well.

Appreciate your help