how to replace blank with zero in a metrix

[nayan28apr]

Hi,

I have to replace blank with zero in metrix visual in power bi.

I am showing sales of 3 dimension region, cluster, country as drill down rows against period, month and day wise on the column.

This is the basic formula

Sales for P05 =
CALCULATE(

SUM(fact_global_daily_sales[actual_sales]),

FILTER('Calendar','Calendar'[year_number]=2021),

FILTER('Calendar','Calendar'[period title]="P05")

)

showing results of the table.

When i write
Var MonthlySales = 

CALCULATE(

SUM(fact_global_daily_sales[actual_sales]),

FILTER('Calendar','Calendar'[year_number]=2021),

FILTER('Calendar','Calendar'[period title]="P05")

)

Return

IF(MonthlySales=BLANK(),0,MonthlySales)

or 

IF(NOT(ISBLANK(MonthlySales)),MonthlySales,0)

All table starts showing zero values from above 2 return statements.

The records for which it's showing zero for them there are no rows in the data tables.
How can i first check for such rows and mark them zero before the actual formulas start.

Thanks

[ryan_mayu]

@nayan28apr 

have you tried this

Sales for P05 =
CALCULATE(

SUM(fact_global_daily_sales[actual_sales]),

FILTER('Calendar','Calendar'[year_number]=2021),

FILTER('Calendar','Calendar'[period title]="P05")

) +0

[nayan28apr]

Thanks for the reply.
I have tried as below, but it doesn't work and showing zeros.

This is strange to see it start showing P1, P2 in the column when we add +0

[ryan_mayu]

@nayan28apr 

in your original DAX, you set period title is 05, is that May? Your screenshot shows the result of Jan.

[nayan28apr]

What I want is :

1. This matrix should show latest period information as we proceed further. Like P6 started now and so on.

2. This formula works fine for me but it shows blank. This is basically that combination rows are not present in the data for that day. I want to show 0 when no values.

3. As soon as i apply condition or look to catch this scnarios. All values coming as 0 and metrix started showing values from P1 to P6.

[ryan_mayu]

@nayan28apr 

have you tried to modify the last part of DAX

iF(not(isblank(monthlysales)),monthlysales,0)

[nayan28apr]

Thanks for the reply.
Yes, i tried this initially but not worked as well.

IF(NOT(ISBLANK(MonthlySales)),MonthlySales,0)

As soon as I pass zero, all becomes zero.

[ryan_mayu]

@nayan28apr 

can you share the pbix file after removing sensitive data?

[nayan28apr]

Hi, Thanks for your reply.
Attached sample pbix on the below location:

https://drive.google.com/file/d/1SJPSZConmHt-soh9QoVg98VsSz7cPHxJ/view?usp=sharing

Thanks

[nayan28apr]

Thanks for your reply.

We have P5 starts from May 6th to June5th so it may include May and June also, its not calendar months. Now I add +0 the matrix starts showing from P1 and period title = P5 condition fails.

[nayan28apr]

I have also tried with count of rows in if condition as below,
it identified blank records and only give zero for it. But don't show actual records which has values in it. Issue the same it shows P1 to P3 though we have given P5.

[ryan_mayu]

@nayan28apr 

the fasteset way is to add a visual filter and select P05