problem with dax code in a measure

[Anonymous]

Hello champs,

I have following measure but it doesn't show me values(empty cells), it doesnt say error either. When I remove  (SelectedYear - TopEventYear) > 5 at the bottom , all works correctly , it shows the total, thats fine but I don't need total what I need to achieve extra condition "greater than 5 years from total" . Any idea how to achieve it? In Months would be great as well. which is 60. I tried many ways and now I'm lack of ideas.

Measure:

pokus6 =

VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])  

RETURN  

    COUNTROWS(

        FILTER(

            CecCaseDetail,

            VAR TopFromStateID =

                CALCULATE(

                    SELECTEDVALUE(CecCaseEvent[FromStateOfProceedingID]),

                    TOPN(

                        1,

                        FILTER(

                            ALL(CecCaseEvent),

                            CecCaseEvent[CecCaseID] = CecCaseDetail[CecCaseId] &&

                            CecCaseEvent[ToStateOfProceedingID] <> CecCaseEvent[FromStateOfProceedingID] &&

                            YEAR(CecCaseEvent[EventDate]) = SelectedYear

                        ),

                        CecCaseEvent[EventOrder],

                        DESC

                    )

                )

            VAR TopEventDate =

                CALCULATE(

                    MAX(CecCaseEvent[EventDate]),

                    CecCaseEvent[FromStateOfProceedingID] = 4

                )

            VAR TopEventYear = YEAR(TopEventDate)

            RETURN

                TopFromStateID IN {4} &&

                NOT(ISBLANK(TopEventYear)) &&

                (SelectedYear - TopEventYear) > 5 &&

                CALCULATE(

                    COUNT(CecCaseDetail[CecCaseId]),

                    CecCaseEvent[EventID] = 64,

                    YEAR(CecCaseEvent[EventDate]) = SelectedYear,

                    CecCaseEvent[CecCaseId] = CecCaseEvent[CecCaseId]

                ) && IF(ISBLANK(TopFromStateID), 0, TopFromStateID) &&

                COUNTROWS(

                    FILTER(

                        'CecLeadingCase',

                        'CecLeadingCase'[CecCaseId] = CecCaseDetail[CecCaseId]

                    )

                )

        )

    )

HERE is picture I tried to explain how it works , we need to achieve "the green arrow " greater more than 5 years from the red rectangle. Forgot to write in the picture the Event Date column is CecCaseEvent[EventDate].

 

[rajendraongole1]

Hi @Anonymous  - Add a debug measure to check TopEventYear and SelectedYear separately to confirm they are correctly calculated.

DebugMeasure =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
VAR TopEventDate =
CALCULATE(
MAX(CecCaseEvent[EventDate]),
CecCaseEvent[FromStateOfProceedingID] = 4
)
VAR TopEventYear = YEAR(TopEventDate)
RETURN
"Selected Year: " & SELECTEDVALUE('Calendar'[Date].[Year]) &
" | Top Event Year: " & IF(ISBLANK(TopEventYear), "Blank", FORMAT(TopEventYear, "0")) &
" | Difference: " & IF(ISBLANK(TopEventYear), "N/A", FORMAT(SelectedYear - TopEventYear, "0"))

Modify your measure to handle cases where TopEventYear is blank.

pokus6 =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
VAR TopEventDate =
CALCULATE(
MAX(CecCaseEvent[EventDate]),
CecCaseEvent[FromStateOfProceedingID] = 4
)
VAR TopEventYear = YEAR(TopEventDate)
VAR YearDifference = IF(ISBLANK(TopEventYear), 0, SelectedYear - TopEventYear)
RETURN
COUNTROWS(
FILTER(
CecCaseDetail,
VAR TopFromStateID =
CALCULATE(
SELECTEDVALUE(CecCaseEvent[FromStateOfProceedingID]),
TOPN(
1,
FILTER(
ALL(CecCaseEvent),
CecCaseEvent[CecCaseID] = CecCaseDetail[CecCaseId] &&
CecCaseEvent[ToStateOfProceedingID] <> CecCaseEvent[FromStateOfProceedingID] &&
YEAR(CecCaseEvent[EventDate]) = SelectedYear
),
CecCaseEvent[EventOrder],
DESC
)
)
RETURN
TopFromStateID IN {4} &&
NOT(ISBLANK(TopEventYear)) &&
YearDifference > 5 &&
CALCULATE(
COUNT(CecCaseDetail[CecCaseId]),
CecCaseEvent[EventID] = 64,
YEAR(CecCaseEvent[EventDate]) = SelectedYear,
CecCaseEvent[CecCaseId] = CecCaseEvent[CecCaseId]
) &&
COUNTROWS(
FILTER(
'CecLeadingCase',
'CecLeadingCase'[CecCaseId] = CecCaseDetail[CecCaseId]
)
)
)
)

Instead of checking if the difference is greater than 5 years, check if it's greater than 60 months.

Replace (SelectedYear - TopEventYear) > 5 with MonthDifference > 60. check this.

[Anonymous]

sorry Iam lost now how to implement debug measure. Can you tell me what to do with the debug measure. I tried to fit in but it cause empty cell again