Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
)
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.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |