Current Financial Year

Power BI
1

Hi,

How would create a data item that dynamically updated Current Financial Year with “Yes” or “No”.

I’m using the date table generated using your query.

Regards,
Bala

2

First you would need a column in your Date or Calendar Table called “Fiscal Year” Then you could create the following calculated column for “Current FY”. I believe Sam provides it in his Date Table code document. I will attach it in case you do not have a copy.

   Current FY =
IF (
    YEAR ( NOW () ) = 'Calendar'[Fiscal Year]
        || YEAR ( NOW () ) = 'Calendar'[Fiscal Year],
    Yes,
    No
)

Thanks

Enterprise%20DNA%20Expert%20-%20Small

Power BI Date Table code.txt (2.8 KB)

3

Hi ojones

Sorry, confused. Is there a specific reason to check the same condition
Year( Now()) = Calendar [Fiscal year] twice

Just thinking loud, what will happen in Jan or Feb. say I have fiscal year data from 2016 to 2020 in my date table. My current fiscal year = 2017. From July to December Year ( Now() ) will work and will show 2017.
But in January Year ( Now () ) will become 2018 and it will match to fiscal year 2018 not to 2017. My condition will be true and I will get 2018 data instead of 2017. Is that correct statement or am I missing something here.

Thanks

4

Hi Ojones/Namjan,

I’m facing same issues as this DAX gives me FY19 instead of FY20!

Regards,
Bala

5

Greetings:

To begin we assume 2 things
The date table covers the entire spectrum of the financial data dates.
There is an active relationship between the 2 tables back to dates[date].

Next verify the correct field name to be used in the solution per text files attachment (fiscal year = FY).

Create 2 measures to return the FY and the Current FY

Measure 1:  Curr FY = CALCULATE([Fiscal Year], Dates[Date]=TODAY())
Measure 2: Fiscal Year = SELECTEDVALUE(Dates[FY])

Perform a validation test in the table using the above measures

Validate = IF([Fiscal Year] = [Curr FY], "Yes", "No")

Combine the 2 measures into 1 expression/1 column in the data table.

test = IF(RELATED(Dates[FY]) = CALCULATE(SELECTEDVALUE(Dates[FY]), Dates[Date]=TODAY()), "Yes", "No")

Clean-up, remove the draft calculations and keep only the test as shown on the attachment.
For sanity check you might create a calculated column showing the Fiscal Year next to the test as follows:

Fiscal Year = RELATED(Dates[FY])

Hope this will do the job for you. Have a great day.

Test%20Current%20FY
Test Current FY.JPG720×303 41.7 KB

6

Here is solution I got to work with creating 3 calculated columns:

Current FY =
IF (
    YEAR ( NOW () ) = 'Calendar'[Fiscal Year] - 1
        || YEAR ( NOW () ) + 1 = 'Calendar'[Fiscal Year],
    1,
    0
)

Fiscal YTD =
IF ( [Current FY] = 1 && [Fiscal Day] <= [Todays Fiscal Day Of Year], 1, 0 )
Fiscal YTD = IF([Current FY]=1 && [Fiscal Day]<=[Todays Fiscal Day Of Year],1,0)

Fiscal PYTD =
IF (
    YEAR ( TODAY () ) - 1 = [Year]
        && [Fiscal Day] <= [Todays Fiscal Day Of Year],
    1,
    0
)

FYI- I did this for a client that has FY from April-March
Thanks
Enterprise%20DNA%20Expert%20-%20Small

7

I think yours will apply CY to FY18 and FY 20!

I was able toa chieve what i wanted through the syntax below

Current FY = 
IF ( 
    AND('Date'[Fiscal Month Number]>=7|RIGHT('Date'[FY]|2)=RIGHT(YEAR(TODAY())+1|2))
	|"Y"|
    IF ( 
    AND('Date'[Fiscal Month Number]<=6| RIGHT(YEAR(TODAY())|2) = RIGHT('Date'[Year]|2)) 
    |"Y"|"N"
    )
    )
8

Was just having another look at your solution and noticed an issue( One of your “Y” needs to be an “N”, or every day of the year will equal Y). It needs to look like this in order for it to work correctly.

Current FY = 
IF ( 
    AND('Date'[Fiscal Month Number]>=7|RIGHT('Date'[FY]|2)=RIGHT(YEAR(TODAY())+1|2))
	|"Y"|
    IF ( 
    AND('Date'[Fiscal Month Number]<=6| RIGHT(YEAR(TODAY())|2) = RIGHT('Date'[Year]|2)) 
    |"N"|"N"
    )
    )

Thanks
Enterprise%20DNA%20Expert%20-%20Small