Replace NULL with 0 but only between Min and Max value

[soldous]

Hi all,

I have 4 tables:

• FactInvoice which includes monthly invoicing data
• DimOrder for all orders which have been invoiced
• DimMaterial for all materials in the orders
• DimInvoiceDate which is a date dimension

But there could be months with no invoices. 

Now I have 2 visualizations:

• Total charges (sum of all invoicing data) per months
• Total charges per months and material

I need to fill months without data with 0. But the simple 

IF(ISNULL([Charges]),0[Charges]))

doesn't do what I need because it puts zeros across the whole date dimension. I need to fill the gaps only between first and last invoicing data.

The second problem with this measure is that it' doesn't filter materials in the second visualization and shows all of them from DimMaterial with zeros.

Could you please help?

Thanks a lot

[kentyler]

Could you wrap your IF() with a another IF

IF(ISNULL([Charges]),0[Charges]))

IF(someDate >= FactInovice[start_date] && someDate <=FactInvoice[end_date], IF(ISNULL([charges]),0[Charges]))

[soldous]

I'm still not able to manage this. I could provide dummy pbix if needed.

I don't know how can I check the date in the first IF....I have MIN(FactInvoice[Date]) and MAX(FactInvoice[Date]) but what I need to compare with those?

On the visualisation's x-axes I have DimInvoiceDate[YearNumber] - DimInvoiceDate[MonthNumber] hierarchy and these tables are connected with FactInvoice[Date] (YYYYMM) - DimInvoiceDate[DateKey] (YYYYMM)