Forum Discussion

icturion's avatar
icturion
Resolver II
4 years ago
Solved

exclude future dates from measure

Hi,

 

I am trying to graph the performance of the current year compared to a year earlier in that period. I then included a line showing the difference in percentage. The problem I run into is that the percentage for the future is shown in the chart as 0%, with the result that my yearly average is also too low. How can I ensure that the percentage is not calculated for the period to come?

 

 

Im yousing the following measures:

  • Uitgevoerd = (countrows(table)
  • Uitgevoerd PY = calculate([# total AC], DateADD('calendartable'[datecolumn].[Date], -1, YEAR))
  • # vs PY = DIVIDE([Uitgevoerd] , [Uitgevoerd PY], 0)

hopefully someone knows the solution

Need Help
  • icturion's avatar
    icturion
    4 years ago

    thanks for your response, it pointed me in the right direction. I solved it like this now:

     

    Measure 2 =
    VAR LastActualDate =
    LASTNONBLANK (
    'table'[datumcolumn].[Maand],
    [uitgevoerd]
    )
    RETURN
    CALCULATE (
    DIVIDE ( [Uitgevoerd], table[Uitgevoer PY], 0 ),
    LastActualDate
    )

2 Replies

  • itchyeyeballs's avatar
    itchyeyeballs
    Impactful Individual
    4 years ago

    Hi,

     

    We use a measure like this:

     

    Measure2 =
    CALCULATE (
    [Measure1],
    FILTER (
    ALL ( 'DateTable'[DateCol]),
    'DateTable'[DateCol] <= MAX ( ( 'DateTable'[DateCol]) )
    )
    )+0
    • icturion's avatar
      icturion
      Resolver II
      4 years ago

      thanks for your response, it pointed me in the right direction. I solved it like this now:

       

      Measure 2 =
      VAR LastActualDate =
      LASTNONBLANK (
      'table'[datumcolumn].[Maand],
      [uitgevoerd]
      )
      RETURN
      CALCULATE (
      DIVIDE ( [Uitgevoerd], table[Uitgevoer PY], 0 ),
      LastActualDate
      )