DAX YTD problem

[Anonymous]

Hello guys!

I am new to Power BI and I need some help.

YTD calculation stops working correctly when I add  Date column not from the Date Hierarchy.

This is my DAX formula:

Actual YTD 2 = CALCULATE(TotalYTD(SUMX('Fact',(IF('Fact'[Scenario ID]=1,'Fact'[Value]*0.3,0))),'Date'[Date]))

It gives correct result when I show in the Table only Actual YTD 2, 'Date'[Date] and other dimension, for example:

But when I add Date field from another linked table, it works incorrectly, treats new column as a dimension and the number of rows is multiplied:

Joins between tables are configured correctly the problem is in calculation as I think. Could you maybe help how to solve it?

I want to ınclude ın my Vız date from the 'Fact' table, not from the separate Date hierarchy.

 https://app.powerbi.com/groups/me/reports/38338827-c863-4516-a1ab-bf4e5a0fd6c6?ctid=b41b72d0-4e9f-4c...

[Anonymous]

Thanks!

[daXtreme]

@Anonymous 

The rules of correct PBI model design dictate that:

1. You should hide your fact table's columns.

2. You should only slice by dimensions. I can't stress this enough.

3. Your model should be a star-schema.

If you stick to these golden rules, you won't have problems.

[amitchandak · Accepted solution]

@Anonymous , try like the example. The two comments at end are alternate

YTD QTY forced= 
var _max = //or maxx(allselected('Order'),'order'[Date]) //max date in table
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))