cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
semihyildirim Frequent Visitor
Frequent Visitor

Count of Active Contracts

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Count of Active Contracts

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 () )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
semihyildirim Frequent Visitor
Frequent Visitor

Re: Count of Active Contracts

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 

 

semihyildirim Frequent Visitor
Frequent Visitor

Re: Count of Active Contracts

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