topic Re: DAX YTD problem in DAX Commands and Tips

DAX YTD problem

Anonymous — Mon, 08 Aug 2022 14:53:10 GMT

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-4c26-8a69-f949f367c91d&pbi_source=linkShare

Re: DAX YTD problem

amitchandak — Mon, 08 Aug 2022 15:42:33 GMT

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

Re: DAX YTD problem

daXtreme — Mon, 08 Aug 2022 15:43:39 GMT

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.

Re: DAX YTD problem

Anonymous — Mon, 08 Aug 2022 15:45:53 GMT

Thanks!