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

[bsas]

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?

[v-huizhn-msft]

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

[bsas]

Hi @v-huizhn-msft,

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

[v-huizhn-msft · Accepted solution]

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

[bsas]

Thanks Angelina.

[v-huizhn-msft]

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