cancel
Showing results for
Did you mean:
Frequent Visitor

## Disconnected period ytd

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.

1 ACCEPTED SOLUTION
Super User

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"))
)

3 REPLIES 3
Frequent Visitor

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

Super User

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"))
)

Frequent Visitor

anybody have a idea

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors