Forum Discussion

pmhoang94's avatar
pmhoang94
Helper I
9 years ago

Measure YTD

Hi all,

 

I have a issue with measure YTD when I filter Date.

I have a table SalesInOut(MonthYear, DateID, Amount, Quantity, SalesType, FreeItem, YearID, PeriodID).

I create a clustered column chart with

   - axis: MonthYear,.

  - value: 

Sales Out Value = CALCULATE(SUM(SalesInOut[Amount])/1000000,SalesInOut[SalesType] = "O", SalesInOut[FreeItem] = FALSE())

I create a KPI with:

   -  Indicator: 

ABSValSI = 
  var x  =   CALCULATE(MAX(SalesInOut[DateID]), ALL(SalesInOut[DateID]))
  return   CALCULATE(SUM(SalesInOut[Amount])/1000000,FILTER(  
ALLSELECTED(
       SalesInOut),     
      [YearID] = YEAR(x) && [PeriodID] <= MONTH(x)
),SalesInOut[SalesType] = "I", SalesInOut[FreeItem] = FALSE())

  - Trend axis: DateID

 

When I do not select column in clustered coumn chart, value of KPI is true.

When I select column in clustered coumn chart, value of KPI is false.

 

 

Not select: true

Select column MonthYearL 05-2017: value is fail

 

Can anyone help me?

Need Help

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi pmhoang94,

     

    Did you verified the data when we applied the filter on the table?

    1. X will return the max date in the whole table. For example, 20181231. 

     var x  =   CALCULATE(MAX(SalesInOut[DateID]), ALL(SalesInOut[DateID]))

     If we choose 05-2017, this formula will return blank.

    [YearID] = YEAR(x) && [PeriodID] <= MONTH(x) 

     2. SalesInOut[SalesType] = "O" while SalesInOut[SalesType] = "I" in the KPI. 

    Maybe these items upper will lead this problem.

     

    Best Regards!

    Dale