Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Measures based in Multiple Filters

Hi all,

 

I've been having trouble with a specific measure that has to change depending on the selected values on a Filter.

 

I've got a calendar table which has the following fields:

Calendar[Date], Calendar[Year], Calendar[MonthNumber]

 

Another table with these fields:

NET Sales [USD], NET Sales [Date]

 

And another with these ones:

Forecast [USD], Forecast [Date]

 

(As a clarification, the days on the "Date" field in every table shouldn't be taken into account, so the only important parts are the month and the year. Also the dates which are after today's month, doesn't have NET Sales[USD], only Forecast[USD]))

 

The idea is to have a filter with the months and another with the years (which I already have), and the measure has to: If the selected year is less than today's year; or if it is equals to today's year and the month is less than today's month, then you would have to get the SUM of NET Sales[USD] of that month of that year. Else, you would have to get the SUM of Forecast [USD] of that month of that year. 

 

I've done that with this measure:

 

IF(OR(YEAR(TODAY())>SELECTEDVALUE(Calendario[Year]);AND(YEAR(TODAY())=SELECTEDVALUE(Calendar[Year]);MONTH(TODAY())>SELECTEDVALUE(Calendar[MonthNumber])
SUM([NET Sales (USD)]);
SUM(Forecast[USD]))

(The SUM of NET Sales is because you can have more then one sale on the same month, but as I said before, I only care of the months and years)

 

The problem is that when selecting different months, before today's date and after, it only shows the sum of NET Sales[USD] of the selected values, but not the Forecast ones.

 

I think the problem is that SELECTEDVALUE only takes the first value in the filter, so if the first month selected is before today, it will take the "IF" condition as it was before today for all the other selected values.

 

I would like to know how could I evaluate the condition in every selected filter so when selecting all of them, it sums all the NET Sales[USD] when the date is before today, then all Forecast[USD] when the date is after; and then sum the two of them to show the entire year.

Need Help

3 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago
    Anonymous wrote:

     

    I think the problem is that SELECTEDVALUE only takes the first value in the filter, so if the first month selected is before today, it will take the "IF" condition as it was before today for all the other selected values.

     

    No, the problem is that SELECTEDVALUE only returns a value if there is a single value, if you have multiple values in the current filter context it will return BLANK() by default and BLANK() is numerically equivalent to 0. So your if condition will evaluate to true and only return the sales, not the forecast.

     

    One possibly solution is to do a SUMX over your calendar table and nest your current expression inside that

    eg.

     

    SUMX( Calendario
    ;
    IF(OR(YEAR(TODAY())>SELECTEDVALUE(Calendario[Year]);AND(YEAR(TODAY())=SELECTEDVALUE(Calendar[Year]);MONTH(TODAY())>SELECTEDVALUE(Calendar[MonthNumber])
SUM([NET Sales (USD)]);
SUM(Forecast[USD]))
    )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      d_gosbell wrote:

       

      SUMX( Calendario
      ;
      IF(OR(YEAR(TODAY())>SELECTEDVALUE(Calendario[Year]);AND(YEAR(TODAY())=SELECTEDVALUE(Calendar[Year]);MONTH(TODAY())>SELECTEDVALUE(Calendar[MonthNumber])
SUM([NET Sales (USD)]);
SUM(Forecast[USD]))
      )

       

      I tried this but it didn't work. It didn't work with simple filters either, so I had to add a CALCULATE(Calendar;...) and the code in the second parameter.  

       

      Then when I select a single filter it performs just fine, but now if I choose two values, it throws this error: 

      "A table of multiple values was supplied where a single value was expected."

       

      With the first measure I could selected more than one, but with this solution it works with only one at a time.

       

      (A little update: Now the "SUM(Forecast[USD])" is "SUMX(Forecast;Forecast(qty)*Price[USD])" . I don't know if that change things, but just in case.)

      • d_gosbell's avatar
        d_gosbell
        Super User
        7 years ago
        Anonymous wrote:

        I tried this but it didn't work. It didn't work with simple filters either, so I had to add a CALCULATE(Calendar;...) and the code in the second parameter.  

         

        I can't think what would cause this. I'd need to see what you've changed and what the error message was. I had to make a lot of guesses about what references were column and which ones were measures and it looks like 

         

        If you could build a small example model that illustrates your issue it be very helpful in solving it.

        (see How to Get Your Question Answered Quickly - Microsoft Power BI Community)