Conditional date to calculate the last exact fiscal year

[sgaydarska]

I have a dim_date table with the following columns:

key_date - for example, 1

fiscal_year - for example, 2020

fiscal_year_start_date - for example, 20200202

fiscal_year_end_date - for example, 20210130

fiscal_period, for example, 202001 (my fiscal year begins in February)

fiscal_period_start_date, for example, 20200202 (for fiscal period 2020 1)

fiscal_period_end_date, for example, 20200229 (for fiscal period 2020 1)

fiscal_week - for example, 202001

fiscal_week_start_date, for example, 20200202 (for fiscal year 2020 week 1)

fiscal_week_end_date, for example, 20200308 (for fiscal year 2020 week 1

date 3/2/2020

Except for "date", which is a date variable, the rest are integers. This is how you configure the data table in the database.

I also have a table fact_transactions where I have:

key_date_trade- date of the transaction; whole number

value: transaction value, currency

dim_date and fact_transactions have many relationships:1 (key_date and key_date_trade).

I'm calculating Trade (sum of transaction value) for last year as follows:

Trade_LY calculate(sum(fact_transactions[value],dateadd(dim_date[date],-1,Year)

This is the problem I have. Below is a snapshot of my YTD 2020 per week:

These are the weeks of the 2019 financial year that should correspond to the Trade_LY in the table above:

As you can see they are different because the fiscal week 202001 is 2/2/2020 - 8/2/2020 and the fiscal week 201901 is 3/2/2019 - 2 9/2/2019 and Trade_LY compares 2/2/2020 - 8/2/2020 to 2/2/2019 - 9/2019. However, 2/2/2019 is in 201852 fiscal week for me. The following is the example:

The dahboard is based on tax dates (year, period, week and never date) and that's how all calculations and filters have to work. How do I change the Trade_LY formula to accurately calculate it?

Thanks for the help!

[sgaydarska]

I have a dim_date table with the following columns:

key_date - for example, 1

fiscal_year - for example, 2020

fiscal_year_start_date - for example, 20200202

fiscal_year_end_date - for example, 20210130

fiscal_period, for example, 202001 (my fiscal year begins in February)

fiscal_period_start_date, for example, 20200202 (for fiscal period 2020 1)

fiscal_period_end_date, for example, 20200229 (for fiscal period 2020 1)

fiscal_week - for example, 202001

fiscal_week_start_date, for example, 20200202 (for fiscal year 2020 week 1)

fiscal_week_end_date, for example, 20200308 (for fiscal year 2020 week 1

date 3/2/2020

Except for "date", which is a date variable, the rest are integers. This is how you configure the data table in the database.

I also have a table fact_transactions where I have:

key_date_trade- date of the transaction; whole number

value: transaction value, currency

dim_date and fact_transactions have many relationships:1 (key_date and key_date_trade).

I'm calculating Trade (sum of transaction value) for last year as follows:

Trade_LY calculate(sum(fact_transactions[value],dateadd(dim_date[date],-1,Year)

This is the problem I have. Below is a snapshot of my YTD 2020 per week:

These are the weeks of fiscal year 2019 that should correspond to the Trade_LY in the table above:

As you can see they are different because the fiscal week 202001 is 2/2/2020 - 8/2/2020 and the fiscal week 201901 is 3/2/2019 - 2020 9/2/2019 and Trade_LY compares 2/2/2020 - 8/2/2020 to 2/2/2019 - 9/2/2019. However, 2/2/2019 is in 201852 fiscal week for me. The following is the example:

The dahboard is based on tax dates (year, period, week and never date) and this is how all calculations and filters have to work. How do I change the Trade_LY formula to accurately calculate it?

Thanks for the help!

[MFelix]

Hi @sgaydarska ,

Have you tried to use SAMEPERIODLASTYEAR?

Trade_LY = calculate(sum(fact_transactions[value]),sameperiodlastyear(dim_date[date]))

[sgaydarska]

Hi,

I tried but this still didn't solve for the fact that my fiscal daes are custom i.e. fiscal day 1 2019 is 2/2/2019 and fiscal day 1 2020 is 3/2/2020. The post I marked as solution though solves for it accurately.

Thanks,
Sani

[Anonymous · Accepted solution]

It's very easy actually. You can't use the standard time-intel functions in your formulas because they work on the standard calendar and you need a custom financial calendar. You have to use your own filters and functions to achieve this. For your LY measures, 

you have to number days in your financial years accordingly. So, say you have a column that for each date returns its sequence number within the financial year. Then an LFY measure is simple to write (on condition that your current period is fully contained in one financial year, which normally always is the case):

[Base Measure LFY] = // (L)ast (F)inancial (Y)ear
var __currentYear = selectedvalue( Calendar[FinancialYear] )
return
calculate(
	[Base Measure],
	VALUES( Calendar[DayInFinancialYearSequenceNumber] ),
	Calendar[FinancialYear] = __currentYear - 1,
	all( 'Calendar' )
)

Best

D

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...