cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Calculate YTD between dates of the same table

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

2 REPLIES 2
Microsoft
Microsoft

@jordi012

 

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

 

Best Regards,

Herbert

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.

 

ContractIdAnual FeeDateOpeningDateClosing
1301/1/201530/01/2016
2202/1/2015 
3103/1/201617/01/2017
4151/4/2014 
5201/5/201625/07/2016
64001/02/2015 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors