Get last Month/Year with no blank values

[gdps_vc]

Hello there!

Below I have a part of a KPI database that is loaded in Power BI:

VCPRJ	Type	Value	Data
VCPRJ0005519	Real	7329875,38	01/04/2023
VCPRJ0009615	Plan	54582,24235	01/04/2023
VCPRJ0005519	Plan	5291475,47	01/04/2023
VCPRJ0009615	Real	54300,31021	01/04/2023
VCPRJ0005519	Plan	5291475,47	01/05/2023
VCPRJ0005519	Real	5291475,47	01/05/2023
VCPRJ0009615	Real	34536,13893	01/05/2023
VCPRJ0009615	Plan	34536,13893	01/05/2023
VCPRJ0005519	Real	5291475,47	01/06/2023
VCPRJ0009615	Real		01/06/2023
VCPRJ0005519	Plan	5291475,47	01/06/2023
VCPRJ0009615	Plan	31576,39431	01/06/2023

I need to show on a card the last Month/Year that has all values in the Value column filled in (not null/blank), considering only Type = Real.

For the example given above, the card should show 05/2023.

Any ideas on how to solve this?

[rubayatyasmin]

Hi, @gdps_vc 

1. Create a new calculated column that filters the data to only include rows where Type = Real and the Value is not null or blank.
2. Create a measure to determine the last month/year that has all values filled in for Type = Real.
3. Use a card visual to display the result.

example code for the calculated column

RealValueFilled = IF(NOT(ISBLANK([Value])) && [Type] = "Real", 1, 0)

for measure

LastMonthWithAllRealValues =
VAR LastMonthYear =
CALCULATE(
MAX('Table'[Data]),
FILTER('Table', 'Table'[RealValueFilled] = 0)
)
RETURN
EOMONTH(LastMonthYear, -1)

this should be enough to give you an idea. 

I hope this helps