DATESYTD only providing current value instead of cumulative value

[paiello1]

I am trying to use DATESYTD to calculate the YTD revenue for an apartment property. The DATESYTD function works as expected until I try to filter out properties that are not stabilized in the context month. When I add this filter, I get the current month's revenue rather than the cumulative value.

Here is my measure:

Actual YTD TEST = CALCULATE ( [Actual Amount], 
                              ALL ( 'Date' ), 
                              DATESYTD ( 'Date'[Date] ), 
                              FILTER ( 'Income Statement', RELATED ('Property Information'[Stabilized Date]) <= MAX ( 'Date'[Date] )
                              ) 
                            )

There is a 1:Many relationship between the Date table and the Income Statement table with the Date column.
There is a 1:Many relationship between the Property Information table and the Income Statement table with the Property name.

There is a column in the Property Information table called Stabilized Date which is the date the property stabilized.

I thought Actual Amount would be summed for each month YTD and then the resulting table would filter for any properties whose stabilization date was before the context date. Here is a screenshot of the matrix with the Actual Amount, Actual YTD (without the filter on the income statement), and the Actual YTD TEST (with the filter on the income statement). As you can see, the Actual Amount YTD Test is showing the current month values rather than the cumulative YTD values.

I am relatively new to DAX, but I generally have been able to search, learn, and fix issues I am having. Unfortunately, this one has me stumped and frustrated.

[v-angzheng-msft]

Hi, @paiello1 

There seems to be an incorrect date in the above Excel file.

If there is no problem with the above logic, then the result of the filtered date calculation may be correct.
This is difficult to get a solution unless you could provide the expected results.

Could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sevenhills]

Curious, what results you see when you try these measures?

M1 = Sum (Table1[Actual Amount])

YTD M1 = Calculate( M1, datesytd('Date'[Date], "31/12"))

[paiello1]

These two measures work as expected. For M1, I get the same values as shown in the Actual Amount column in my original post. For YTD M1, U get the same values as shown in the Actual YTD column in my original post. When I add the property filtering logic in a new measure called YTD M1 TEST, I get the same incorrect results as shown in the Actual YTD TEST column in my original post.

[sevenhills]

Try this?

M3 =
Calculate( Sum (Table1[Actual Amount]),
        FILTER ( Table1, RELATED ('Property Information'[Stabilized Date]) <= MAX ( 'Date'[Date] ),
       datesytd('Date'[Date], "31/12")
)

or

M3 =
Calculate( Sum (Table1[Actual Amount]),
       datesytd('Date'[Date], "31/12"),
        FILTER ( Table1, RELATED ('Property Information'[Stabilized Date]) <= MAX ( 'Date'[Date] )
)

If this is not working give relationship diagram and sample data for tables. Remove sensitive info.

[paiello1]

I really appreciate your help. I attached a spreadsheet that has individual tabs for each of the relevant tables. The spreadsheet also includes a Relationship tab to show the relationships between the tables. Many thanks for the help on this confusing issue.

Here is a link to the spreadsheet: Revised Power BI Tables Link 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[paiello1]

Thanks again for your help. I revised the link in my prior post. Hopefully you will be able to access it now.

[Ashish_Mathur]

Hi,

Sharing the download link of your PBI file will help.  Also, please clearly show the problem in the PBI file and the expected result.