N-1 closing value recovery problem if fiscal period is different from 12 months

[PhilippeMuniesa]

Hello, 

I have a project that presents financial aggregates according to fiscal years.

Everything works when the exercises have the same closing month

ex: 12/31/20 - 12/31/19 - 12/31/18

On the other hand, I cannot find the solution when a financial year does not have the same closing date.

Ex: 12/31/20 - 12/31/19 - 06/30/19

In this example, the fiscal year ended 12/31/19 for a period of 6 months.

The measure to display the aggregate value at the close date is:

     Montant_signé_agreg_mes: = SUM ('Table_FEC_agrégé' [Montant_signé_agreg])

The measure to calculate the value of the aggregate at the end of the previous year is:

     N-1 Mt_sign: = CALCULATE ([Montant_signé_agreg_mes]; SAMEPERIODLASTYEAR (Gestion_exercices [Date_Cloture])

I tried different solutions, but I can't get the value of N-1.
The Gestion_exercices table includes for each exercise:
- The opening date ex: 07/01/2019 [Date_Ouverture]
- Closing date ex: 12/31/2019  [Date_Cloture]

- The closing date of the previous financial year ex: 06/30/19   [ClotureN_1]

for the above example, the measure

TEST DATE N_1: = MIN (Gestion_exercices [ClotureN_1]), returns to me well for each of the years 12/31/19, 06/30/19 and 06/30/18

On the other hand, the measure

TEST N-1 Mt_sign: = CALCULATE ([Montant_signé_agreg_mes]; Gestion_exercice [ClotureN_1]) returns me the value of 12/31/19, and not that of 06/30/19

If you need it, i can put this project in a dropbox file

Thank you for your help

Thanks a lot

[Anonymous]

If you're working with a non-standard fiscal calendar, then you have to create your own logic for time calculations and not rely on the in-built time-intel functions. Easy as that. For this you need a properly built fiscal calendar (custom calendar). Here's a page that can give you the information you most likely need: Time patterns – DAX Patterns

Look for "Custom time-related calculations."