Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |