Forum Discussion

ngocnguyen94's avatar
ngocnguyen94
New Member
3 years ago

Problems when calculating Cumulative value by DATESYTD

Hi,

I just started to learn PBI and I got some problems when calculating cumulative with DATESYTD, hope sb can help plz.

I got the Actuals data (from Jan-2020 to July-2020), main fields are Date column (1-Jan, 1-Feb, 1-Mar, and so on), and Actual number.

I created a Calendar table by Calendarauto. I got the table with dates from 1-Jan-2020 to 31-Dec-2020, and I created some measures as below:

  1. YTD Actual 1 = CALCULATE(SUM(Actuals[Actual]), DATESYTD(Actuals[Date]))
  2. YTD Actual 2 = CALCULATE(SUM(Actuals[Actual]), DATESYTD(CalendarTable[Date].[Date]))
  3. YTD Actual 3 = CALCULATE(SUM(Actuals[Actual]), FILTER(ALLSELECTED(CalendarTable[Date]),ISONORAFTER(CalendarTable[Date],MAX(Actuals[Date]), DESC)))
     

The Table 1 and 2 were created to calculate cumulative values

The measure 3 is still good in both, but measure 1 are not working in two tables (only show data of the month, not cumulative), and measure 2 got same problems in table 1.

I only changed one column of two tables, from Date -> Date hierachy (Month).

 

Can someone help me to explain why the differences occur?

Why this happen when I change the field Date in DATESYTD and the Date/Date Hierarchy column?

Thank you so much

x

 

 

 

1 Reply

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi ngocnguyen94 function DATESYTD is one of the time intelligence functions in DAX. The best usage of this / these function/s is to be used on Calendar / Date table as other usage is hard to explain. In your example use just Measure 2 for YTD amounts as best practice. Hope this help