Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX YTD problem

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

 

  • amitchandak's avatar
    amitchandak
    4 years ago

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    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.