How to add Missing rows and add zero % for missing rows

[Surendra_thota]

Hi All

we are gettig data from the table for every month , if  some month is missing we need add row . for example 

Reading towards complaince / Monthly Frequency Target = complaince%   ( >100%  is equal to 100%)

for missing rows we need to consider   0/1 = 0 %  ,   we need to aggregate fro year with 0 percentage  for missing rows. how to achieve that 

[v-jiascu-msft]

Hi @Surendra_thota,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

Hi @Surendra_thota,

These missing months don't exist, so let's create them. (Modeling -> New Table -> input this formula)

Date =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ),
        "Month1", EOMONTH ( [Date], 0 )
    )
)

Create a relationship from "Month1" to "month";

Then join the two tables together.

Table 2 =
NATURALLEFTOUTERJOIN ( 'Date', 'Table1' )

 

Best Regards!

Dale

[Nakul93]

I have a similar issue wherein I have to add missing seconds row as zero. What changes should be made to the DAX code.

[dilumd]

Hi

You can do this in query editor (right click on the relevant column) and replace "null" with "0" as shown below.

Hope this helps

[Surendra_thota]

It is a measure ,it's not a column

[dilumd]

Then you can right the measure with IF function which will return 0 if your logic is not true.

[Surendra_thota]

That is not working as there is no row in database, if you my screen shot there are no records for Jan , Feb , March etc but we need to values reading for compliance =0 and monthly frequency target =1 and complaice =0% for those missing rows