Measure with Sum using Calculate and IF not showing totals correctly in power bi

0
votes

I have a requirement in power bi to display the amount value only for the minimum date selected by the user in the slicer. So created below measures..

  1. For capturing the minimum selected range

    StartDate = CALCULATE(min(‘DATE’[DATE]),ALLSELECTED(‘DATE’[DATE]))

  2. For Displaying the amount only for the minimum date.

    OP = if(SELECTEDVALUE(‘DATE’[DATE]) = [StartDate], CALCULATE(sum(MEASUREMENTS[OPENING_BASE_VAL]),DATESBETWEEN(‘DATE’[DATE],[StartDate],[StartDate])),0)

I am getting desired output, but the grand-total for this measure becomes 0 as show in below image. Any help much appreciated.

enter image description here

1
powerbidaxpowerbi-desktopmeasure

1 Answers

2
votes

You are getting 0 in the total because SELECTEDVALUE(‘DATE’[DATE]) in your second formula returns blank, so your IF expression reads as " IF BLANK = [Start Date]", which is always false.

SELECTEDVALUE is a syntax sugar for the following code: IF HASONEVALUE(DATE’[DATE]) then return Date[Date] else return blank. Since in the total you have many values (all dates), you are getting a blank.

To solve the issue, you need to iterate by dates.

OP =
VAR StartDate = [StartDate]
RETURN
SUMX(
  VALUES(Date[Date]),
  IF(Date[Date] = StartDate, CALCULATE(SUM(MEASUREMENTS[OPENING_BASE_VAL])), 0))

Here, we first save start date into a variable to avoid calculating it multiple times. Then we create a list of dates using VALUES function, and use SUMX to iterate this list, date by date. If currently iterated date is a start date, then values are summed, otherwise you get zero.

If instead of zeros you can use blanks, you can use a more optimal/faster code:

OP =
VAR StartDate = [StartDate]
RETURN
  CALCULATE(SUM(MEASUREMENTS[OPENING_BASE_VAL]), KEEPFILTERS(Date[Date] = StartDate) )

Here, we calculate sums only where dates are equal the start date. Since there is no iteration, and there is no "IF", the code is much faster. The total will be the same, only line items will show blanks instead of zeros.