Hello!
I've been dealing with a problem that I haven't been able to resolve. I'll try to explain the best I can:
This is my model:
The part of the model that is involved in thos problem are Projects (Iniciativas), Weights (Pesos) And Progresses (Avances).
One of the visuals that I have in one of the reports is like this:
Basically it represents the total numer of projects in the year you are currently seeing, the total progress of all the projects calculated taking account their weight in the year-month you are currently seeing and the total objective at the end of the year taking into account also the weight of each project (this one is almost always going to be 100 because the sum of weight per project per year is 100, but it may be different).
These are the measures that I wrote to get this:
Projects in the current year:
Nº Iniciativas en el año =
CALCULATE( COUNTROWS(Pesos),
FILTER((Pesos),
Pesos[Año] = MAX(Fechas[Año])
)
)
Accumulated progress by weight (second number):
Avance DD acumulado por peso = SUMX ( Iniciativas, [Avance DD Acumulado en el año] * [Peso DD en el año])
Avance DD Acumulado en el año =
CALCULATE( SUM(Avances[Real Mes DD]),
FILTER(ALL(Avances),
Avances[AñoMes] <= MAX(Fechas[AñoMes]) &&
Avances[Código] = MAX(Iniciativas[Código]) &&
LEFT(Avances[AñoMes], 4) >= LEFT(MAX(Fechas[AñoMes]), 4)
)
)
Peso DD en el año = CALCULATE (
MAX(Pesos[Peso IDD]),
FILTER ( ALL ( Pesos ),
Pesos[Año] = MAX ( Fechas[Año] ) &&
Pesos[Código] = MAX( Iniciativas[Código] )
)
)
And the 3rd value measure, the objective one:
Objetivo DD fin de año por peso = SUMX ( Iniciativas, [Objetivo DD Fin de año] * [Peso DD en el año])
Objetivo DD Fin de año = CALCULATE( SUM(Avances[Objetivo Mes DD]),
FILTER(ALL(Avances),
Avances[Código] = MAX(Iniciativas[Código]) &&
Left(Avances[AñoMes], 4) <= Left(MAX(Fechas[AñoMes]), 4) &&
LEFT(Avances[AñoMes], 4) >= LEFT(MAX(Fechas[AñoMes]), 4)
))
The point is that now, I do need to have this visual but "appliying" a filter. I need to have the same visual but showing the projects that are open, the ones that are closed and the same visual that I showed before, that is the total. The main issue that I'm facing here is that the "is Open" or "is Closed" is something that depends on the progress that a project have each month. A project finished is that that in a year, have reached 100% of progress. Is not something that I could create in a calculated column because the state changes depending on the year and month you are seeing.
I created a simple measure that represents the status of a project, and when I put it in a table, it shows it correctly per project depending on the year-month I select (Basically if progress accumulated in year then):
Iniciativa Estado DD = IF([Avance DD Acumulado en el año] > 0.99, "Cerrada", "Abierta").
So, I've tried to duplicate the visual above, the one with the 3 measures, and apply a filter to those visuals so I could reuse the same visual with the same measures, but filtered by the status of the project. Unfortunately, it is not working.
Then I though, ok, lets create another 6 measures. Same like the 3 ones that I had, but taking into account only the projects that matches the condition determined by the status measure. And here is where I am stuck. Btw, I am not an expert in power Bi so maybe it is much simpler than I think.
How can I get this measure (the projects count in the year):
Projects in the current year:
Nº Iniciativas en el año =
CALCULATE( COUNTROWS(Pesos),
FILTER((Pesos),
Pesos[Año] = MAX(Fechas[Año])
)
)
count only the projects with status "open" (abierta) which is get by this other measure?:
Iniciativa Estado DD = IF([Avance DD Acumulado en el año] > 0.99, "Cerrada", "Abierta").
I'm a bit lost, any help is highly apreciated.
Thanks in advance,
Regards
