Calculate YTD between dates of the same table

[jordi012]

Hi,

I am having a little trouble caculating the YTD amounts between 2 dates of the same table. Looks like Power BI Modeler, only accepts 1 relationship to the Time_Dim (Calendar table). Any Ideas how to write that in DAX?? this is the code I have tried so far without success...Help will be immensely appreciated.

YTDBetween2Dates= Calculate (
 sum (QuotesAnualTest[Amount]) ;
  FILTER ( QuotesAnualTest ;
    COUNTROWS ( FILTER( VALUES(' Time_Dim'[DateId]) ;
      QuotesAnualTest[StartDateId] <= Time_Dim[DateId] &&
      QuotesAnualTest[EndDateId] >= Time_Dim[DateId] ))
    > 0)
  )

[v-haibl-msft]

@jordi012

I’m not very clear about your requirement. Could you please give some sample data and the expected output result?

Best Regards,

Herbert

[jordi012]

Of course I can. I think I can explain myself a little beter... 🙂 We have the following Data regarding contracts. Every contract has a date when it was signed and some of them where signed off or cancelled, and a Fee.

I have a Contracts_Fact table that include the Date of Opening and the Date of Closing the contract, linked with the Time_Dim in Power BI.

Expected: I need a bar chart showing the value of the fees every year taking into account that some contracts have expired and do not need to be summed up any longer. Actually is not a YTD, it's rather showing the amount of fees every year of the valid contracts that I have. For example, in the table given, the contract number 1, should show 30 euros each month of 2015, until the 30th of January 2016, after that point in time its value should be 0 (because the contracts is closed)

I give you an example of the data that I expect to plot on the bar chart:

Month               Value of Contracts

January 15:           30+20

February 15:          30+20

...

February 16:        20+10 (contract 1 was closed)

...etc

On the table, the bank values on the column mean that the contract has not been closed.

ContractId	Anual Fee	DateOpening	DateClosing
1	30	1/1/2015	30/01/2016
2	20	2/1/2015
3	10	3/1/2016	17/01/2017
4	15	1/4/2014
5	20	1/5/2016	25/07/2016
6	40	01/02/2015