Metric value has value 0 and does not perform the sum of the last 6 months

[Zulax]

 Hello, I have a problem, I have the following table

 In the following table I have the column with the amount of requirements per month and in the second column the amount of requirement in the last 6 months. 

For example, in the month of December, the requirements would be the sum of 1+5+3+4+4+0+8 (25)

As you may have noticed, in the month of July in the last 6 months it has a value of 0

But this cannot be so, since the value of the last 6 months since July should be 0+8+3+3+5+11+4 (34)

To get the number of requirements, I have the following metric:

Req = 
var reqcreated= CALCULATE(
DISTINCTCOUNT('dw_customerservice thhistorial'[id_req]),
FILTER('dw_customerservice state_req', 'dw_customerservice state_req'[state] == "Created"))
return
IF(ISBLANK(reqcreated),0,reqcreated)
 

And for the calculation of the last 6 months I have the following metric

Req6Month = 
var tmeses = RIGHT(CONCATENATE("0",CONVERT(IF(MAX('dw_proyectos fecha'[NumeroMesSinCero])-6 <= 0, (MAX('fecha'[NumeroMesSinCero])+6),MAX('fecha'[NumeroMesSinCero]) - 6),STRING)),2)
var tanho = IF(MAX('fecha'[NumeroMesSinCero]) -6 <= 0, MAX('fecha'[Anho]) -1, MAX('fecha'[Anho]))
var anhomes = INT(CONCATENATE(tanho,tmeses))
var fechaanho = MAX('fecha'[Anho])
var fechames = MAX('fecha'[NumeroMes])
var fechaconcade = INT(CONCATENATE(fechaanho,fechames))
//The above is the logic needed to calculate the 6 months

return CALCULATE(
[Req],
INT(LEFT(Requerimiento[IdFechaCreacion],6)) >= anhomes,
INT(LEFT(Requerimiento[IdFechaCreacion],6)) <= fechaconcade,
ALL('fecha'))

The problem is that when I have a value equal to 0 (blank) in Req, it does not perform the sum of the last 6 months in Req6Month

I need your help since I ran out of ideas on how to work this case. Thanks

[amitchandak]

@Zulax , with help from date table create a measure like

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

[Zulax]

Hello, I appreciate your possible solution, but it is not what I am looking for, my problem is not focused on how to calculate the values ​​of the last 6 months, but rather how can I work with the value 0 of a previous metric that does not allow the calculation of the last 6 months in that month. 

In short, how do I do when the requirements calculation metric has a value of 0, I can calculate the values ​​of the last 6 months because in the table it can be seen that the logic works well except in the case when one month has a value 0.

[AilleryO]

Hi,

If your problem is just with null (blank) values, then maybe you can replace them in Power Query by 0.

Unlike Excel, Power BI makes the differrence between null (blank) and 0.

If you want to replace don't forget Power Query is case sensitive so you should replace null (lower case) by 0.

Another way if your results are coming from a measure, is to add +0 by the end of your formula, because null+0=0. So it will prevent having null values.

Let us know if this helps