cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jak82 Member
Member

Custom Financial Year

Hi,

 

Our financial year runs from Jun 1st to May 31st is there an 'easy' way to create a new column in the calendar table which has the relvent year against the date.

 

 

In our nominal detail table it has financial year.  What would be the best way to get 

 

Year to Date

Same period Last year

Variance against last year

 

An example of previous year variance is

 

Previous Year Test = CALCULATE(sum('public nomhead'[nominal]),'public nomdet'[nom_year] = ('public nomdet'[nom_year] -1))
 
The above doesnt work though... all help appreciated.

 

 

 

 

Thanks

 

Chris

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Custom Financial Year

you can specify custom financial year in the calculation
https://docs.microsoft.com/en-us/dax/datesytd-function-dax
you will need custom column in your calendar table

FinancialYear = 
VAR __month = MONTH('Calendar'[Date])
VAR __year = YEAR('Calendar'[Date])
RETURN
IF(__month<6, __year-1 & "/" &__year, __year & "/" & __year+1)

SAMEPERIODLASTYEAR will work fine whenever you select year from this

as for YTD, you can use DATESYTD, e.g.

YTD = 
CALCULATE(
    SUM('public nomhead'[nominal]), 
    DATESYTD(Calendar[Date], "31-05")
)

for PY YTD you can just use SAMEPERIODLASTYEAR within calculate that uses the [YTD] measure

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Stachu Super Contributor
Super Contributor

Re: Custom Financial Year

you can specify custom financial year in the calculation
https://docs.microsoft.com/en-us/dax/datesytd-function-dax
you will need custom column in your calendar table

FinancialYear = 
VAR __month = MONTH('Calendar'[Date])
VAR __year = YEAR('Calendar'[Date])
RETURN
IF(__month<6, __year-1 & "/" &__year, __year & "/" & __year+1)

SAMEPERIODLASTYEAR will work fine whenever you select year from this

as for YTD, you can use DATESYTD, e.g.

YTD = 
CALCULATE(
    SUM('public nomhead'[nominal]), 
    DATESYTD(Calendar[Date], "31-05")
)

for PY YTD you can just use SAMEPERIODLASTYEAR within calculate that uses the [YTD] measure

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

jak82 Member
Member

Re: Custom Financial Year

Thanks @Stachu

 

All works great Apart from previous year to date.

 

I have

 

Debit YTD prev year = CALCULATE(sum('public nomdet'[debit]),SAMEPERIODLASTYEAR(DATESYTD('Calendar'[Date],"05/31")))

Example

 

in 2018/2019 it only seems to do the year to date where there is a value in 2018/2019 rather than the months where are no values but values in 2017/2018

 

Example.PNG

 

 

 

jak82 Member
Member

Re: Custom Financial Year

I set the direction in the relatio bship betwen the calendar and fact table as one way as per other forum post and it seems to work.

 

Strange... would be useful to know why

Stachu Super Contributor
Super Contributor

Re: Custom Financial Year

I always try to keep them with single direction as it makes DAX more predictable, here is some explanation on bidirectional relationships in general
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 560 members 4,327 guests
Please welcome our newest community members: