Forum Discussion

DieLem's avatar
DieLem
Helper II
8 years ago
Solved

Calculation Error in Measure

Hi,

 

I have two graphs that look similar but are different.

 

The first shows a target line of registrations with the current YTD registrations as a column below. It was changed to only show the YTD until today and leave the rest of the year blank using this formula: 

 

IncrementTr YTD = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([IncrementTr Total],DATESYTD('Date'[Date],"30/8")),BLANK())

 

This one works fine.

 

I want to replicate this logic on a second graph. The second has a target revenue line and YTD revenue as the columns.

 

 

However this gives an error when I apply the same logic to the revenue YTD formula:

 

Test = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([Amount Total 18A],DATESYTD('Date'[Date],"30/8")),BLANK())

 

Here the error:

I would like the revenue column to also be blank after any date from today like in the first graph.

 

A link to the PowerBI file:

 

https://drive.google.com/open?id=154JP-ZQkBhkGAxt8cEHdgyaj7ZQ1ZKdI (Filename: TrackingExample2)

 

Thanks in advance!

  • Instead of VALUES('Date'[Date]) <= TODAY() you should use MIN('Date'[Date]) <= TODAY()

4 Replies

  • sdjensen's avatar
    sdjensen
    Solution Sage

    Instead of VALUES('Date'[Date]) <= TODAY() you should use MIN('Date'[Date]) <= TODAY()

    • DieLem's avatar
      DieLem
      Helper II

      Thanks so much sdjensen !

       

      Can you explain to me why it would work in one graph but on in the other? Want to understand so I can handle it myself in future.

       

      Thanks!

      • sdjensen's avatar
        sdjensen
        Solution Sage

        It's because you use the formula in another context which cause VALUES to return more than one date which causes the error.

  • Headcount =
    CALCULATE(
        DISTINCTCOUNT(Headcount[Person Number]),FILTER(Headcount,Headcount[Termination Date]=Headcount[Headcount Month])
    )
    +
    CALCULATE(
        DISTINCTCOUNT(Headcount[Person Number]),ISBLANK(Headcount[Termination Date])
    )