cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmhoang94
Helper I
Helper I

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

Capture30.PNG

Select column MonthYearL 05-2017: value is fail

Capture31.PNG

 

Can anyone help me?

1 REPLY 1
v-jiascu-msft
Microsoft
Microsoft

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 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors