Disconnected period ytd

[EdouardPBI]

Hello everyone,

My problem concerns the YTD.

I have 3 tables: Accounting (with date, account number, balance, company name), Date (a standard date table), and a third table, Fiscal Period.

Example:

Company 1_ 01/01/2022 - 31/12/2022

Company 1_ 01/01/2023 - 30/06/2022

Company 2_ 01/06/2002 - 01/07/2022

The Accounting table and Date tables are related to each other.

I have created the following measure:

comptaYTD =

var ste = SELECTEDVALUE(SOCIETE[COD_STE])
var currentdate = max('Calendar'[Date])

var DebutExercice = CALCULATE(min('Exercice Groupe'[Debut Exercice]), filter('Exercice Groupe',currentdate>='Exercice Groupe'[Debut Exercice] && currentdate<='Exercice Groupe'[Fin exercice]) ,filter('Exercice Groupe', 'Exercice Groupe'[Code STE] = ste))

var FinExercice = CALCULATE(max('Exercice Groupe'[Fin exercice]), filter('Exercice Groupe',currentdate>='Exercice Groupe'[Debut Exercice] && currentdate<='Exercice Groupe'[Fin exercice]) ,filter('Exercice Groupe', 'Exercice Groupe'[Code STE] = ste))

var result = CALCULATE([compta], filter(COMPTA,COMPTA[Date compta] >=DebutExercice && COMPTA[Date compta] <= FinExercice))
return

result

I am able to retrieve the correct period "beginning of the year" and "end of the year" for the selected company, but the YTD calculation is not working.

[EdouardPBI]

yes ! thanks you very much, 

but in my case, there is a case where a companies has a 15 month fiscal year 

I try with a duplicate date table (one calendar for each companies and i append all in one table)

thks a lot

[amitchandak · Accepted solution]

@EdouardPBI , 

If you only need YTD you can consider  a measure like

YTD Sales =
Switch(Max(Table[Company]) ,
"Company 1" , CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")),
"Company 2" , CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"06/30")),
"Company 3" , CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))
)

[EdouardPBI]

anybody have a idea