Get fiscal year and month based on customised fiscal start and fiscal end

[starmoonknight]

Hi all,

Though sounds quite straightforward, I've got stuck when trying to get fiscal year and month for a calendar date based on the customised fiscal period.

Here are the two tables:

CalendarDates

Date: date

E.g.

Date

...

25/09/2015

26/09/2015

27/09/2015

38/09/2015

...

**This is a calendar date table calculated based on the MIN project start date and MAX project end date**

I've got another table that stores the fiscal period info

FYDates

FYPeriod: whole number,

PeriodStart: date,

PeriodEnd: date,

FYStart: date,

FYEnd: date,

FiscalYear: whole number,

FiscalMonth: whole number

E.g.

FYPeriod  PeriodStart     PeriodEnd      FYStart           FYEnd           FiscalYear  FiscalMonth 

201501,    26/09/2015,    30/10/2015,    26/09/2015,    30/09/2016,    2015,            1

201502,    31/10/2015,    27/11/2015,    26/09/2015,    30/09/2016,    2015,            2

I'd like to get the fiscal year and month for all the dates in the calendar, but don't know how to use the DAX function to map the calendar date into FYPeriod by comparing with PeriodStart and PeriodEnd. These two tables have no relationship and FYDates is not a continuous calendar.

Usually we could easily get fiscal year and month based on the company fiscal start month (i.e. October); however, in this case, the FY start is not the exact start date of the fiscal start month, which I guess is due to the accounting period and non-working days.

It would be really appreciated if someone could help me out.

Many thanks in advance,

Olivia

[ImkeF]

Hi Olivia,

provided that your calenar table is sorted you should just merge it with the FYDates on "PeriodStart". Keep the default JoinKind (LeftOuter).

Then expand the relevant fields: This will create a lookup returning the correct figures for the first dates of your Fiscal period.

Then check all these new columns rightclick and "FillDown"".

[starmoonknight]

Hi Imke,

@ImkeFThanks a lot for your opinion and the very useful blog!

The thing is that CaledarDates is a calculated table, and it seems that I can not use Merge Queries which is available in Query Editor, that's why I'm kind of stuck.

I guess it shoud be able to use some sort of DAX formula to filter the date by PeriodStart and PeriodEnd, just have no clue how to write the expression. Sorry that I only have very basic DAX knowledge.

Cheers,

Olivia

[ImkeF]

Hi Olivia,

check out this blog: https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/

[starmoonknight]

I've played around with PBI for just half a month, and I know this question could be a silly one. It really helpls me a lot if someone can provide relevant turoails or blog articles.

Many thanks,

Olivia

[ImkeF]

Ken Puls' blog is the best starting point for the query-part in my eyes: http://www.excelguru.ca/blog/