cancel
Showing results for
Search instead for
Did you mean:
Member

## Fill missing months rows with 0 or value in running total measure for correct KPI visualization

Hi All,

I have some sales data per month and goal per month. I need to create KPI view. The problem is that for some months I do not have any sales data but do have goal and when I'm trying to create some measures I receive wrong data.

running total for sales per month

`WonRT = CALCULATE(SUM(opportunity[Closed_booking]),         FILTER(ALLSELECTED(opportunity),MAX(opportunity[ss_closedate]) >= opportunity[ss_closedate]))`

running total for goal per month

`goalRT = CALCULATE(SUM('territory goals'[booking_goal]),          FILTER(ALLSELECTED(opportunity),MAX(opportunity[ss_closedate]) >= opportunity[ss_closedate]))`

I have problem with blank data. How I can fill data in first column as 0 for "red" months and with goal (it is present in other table) with value?

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Fill missing months rows with 0 or value in running total measure for correct KPI visualization

Hi @bsas,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will find workaround quickly and easily. If you haven't, please feel free to ask.

Thanks,
Angelia

5 REPLIES 5
Highlighted
Super Contributor

## Re: Fill missing months rows with 0 or value in running total measure for correct KPI visualization

Hi @bsas,

Please add 0 at the ending of your formula and check if it works fine, please review the following formulas.

```WonRT =
CALCULATE (
SUM ( opportunity[Closed_booking] ),
FILTER (
ALLSELECTED ( opportunity ),
MAX ( opportunity[ss_closedate] ) >= opportunity[ss_closedate]
)
)
+ 0
```

```goalRT =
CALCULATE (
SUM ( 'territory goals'[booking_goal] ),
FILTER (
ALLSELECTED ( opportunity ),
MAX ( opportunity[ss_closedate] ) >= opportunity[ss_closedate]
)
)
+ 0
```

Please respond to me if you have any other problems.

Best Regards,
Angelia

Member

## Re: Fill missing months rows with 0 or value in running total measure for correct KPI visualization

Tahnks for your efforts! I've checked, adding 0 do not work, it gives same result.

Super Contributor

## Re: Fill missing months rows with 0 or value in running total measure for correct KPI visualization

Hi @bsas,

Please create two measure using the formulas below, and check if it works fine.

```WonRT_new =IF(ISBLANK([WonRT]),0,[WonRT])+0

goalRT_new =IF(ISBLANK([goalRT]),0,[goalRT])+0```

Best Regards,
Angelia

Super Contributor

## Re: Fill missing months rows with 0 or value in running total measure for correct KPI visualization

Hi @bsas,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will find workaround quickly and easily. If you haven't, please feel free to ask.

Thanks,
Angelia

Member

Thanks Angelina.