Count of Active Contracts

[semihyildirim]

hi all,

i have an issue to keep track of contracts as of a date, there are  2 tables, one of them is contract table, 3 columns, contract ID, begin date and end date, the other is date table, which has year, date, month, year-month columns and other date attributes. what I need is, on a slicer I keep yyyy-mm  selection where I want to have the calculation that at that given period list of active contracts. please note that all columns has value, there are no nulls, what I tried so far almost all solutions I saw on the forums, I really appriciate if someone could guide me.

kind regards

[v-yulgu-msft]

Hi @semihyildirim,

Please try to create measures:

flag =
IF (
    (
        YEAR ( SELECTEDVALUE ( Table1[Begin Date] ) ) < SELECTEDVALUE ( Table2[Year] )
    )
        || (
            YEAR ( SELECTEDVALUE ( Table1[Begin Date] ) ) = SELECTEDVALUE ( Table2[Year] )
                && MONTH ( SELECTEDVALUE ( Table1[Begin Date] ) )
                    <= SELECTEDVALUE ( Table2[Month] )
        ),
    1,
    0
)
    && IF (
        YEAR ( SELECTEDVALUE ( Table1[End Date] ) ) > SELECTEDVALUE ( Table2[Year] )
            || (
                YEAR ( SELECTEDVALUE ( Table1[End Date] ) ) = SELECTEDVALUE ( Table2[Year] )
                    && MONTH ( SELECTEDVALUE ( Table1[End Date] ) ) >= SELECTEDVALUE ( Table2[Month] )
            ),
        1,
        0
    )

Count ID =
CALCULATE (
    COUNT ( Table1[ContractID] ),
    FILTER ( ALL ( Table1 ), [flag] = TRUE () )
)

Best regards,

Yuliana Gu

[semihyildirim]

Hi,

this solution was partially hepful however I would like to add this flag as column instead of a measure in order to filter the contracts however it doesn't let me to do in this way.

thanks

[semihyildirim]

Yuliana hi, thank you for your reply, I got following error, 

Error Message:
MdxScript(Model) (12, 29) Calculation error in measure 'kontrat'[flag]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

which of the item should be converted to text (or numeric)?

thank you