No. of Jobs between Price changes between two dates

[kkalyanrr]

Hello ,

I'm trying to achieve the result shown in the below pic, I've 2 tables one with price change information and one with regular orders.
So trying to identify the jobs between the price changes.
Please help out.

[v-rzhou-msft · Accepted solution]

Hi @kkalyanrr 

If you build a calculated column it won't be dynamic by selectvalue.

I build a sample with Table1 and Table2 same like yours to have a test.

Please try my measure to achieve your goal.

Measure = 
VAR _MaxDate = MAXX(FILTER(ALL(Table1),Table1[Deposit Price Changed ON]< MAX(Table1[Deposit Price Changed ON])),Table1[Deposit Price Changed ON])
VAR _SelectValue = SELECTEDVALUE(Table2[Deposit])
VAR _SelectMin = MAXX(FILTER(ALL(Table1),Table1[Deposit Price Changed ON]< _SelectValue),Table1[Deposit Price Changed ON])
VAR _SelectMAX = MINX(FILTER(ALL(Table1),Table1[Deposit Price Changed ON]> _SelectValue),Table1[Deposit Price Changed ON])
VAR _SelectCount = CALCULATE(COUNT(Table2[Deposit]),FILTER(ALL(Table2),Table2[Deposit]>=_SelectMin&&Table2[Deposit]<=_SelectMAX))
VAR _Count = CALCULATE(COUNT(Table2[Deposit]),FILTER(ALL(Table2),Table2[Deposit]<=MAX(Table1[Deposit Price Changed ON])&&Table2[Deposit]>=_MaxDate))+0
RETURN
IF(ISFILTERED(Table2[Deposit]),IF(MAX(Table1[Deposit Price Changed ON]) = _SelectMAX,_SelectCount,0),_Count)

No. of Jobs before changed date = SUMX(Table1,[Measure])

Result is as below.

By default:

Select Deposit in Table2. For Example when I select 2020/11/15 it will show values in range(2020/08/06 <2020/11/15<2021/02/01).

If this reply couldn't solve your problem, please show more details about the result you want.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@kkalyanrr , A new column in table 1 can give you count

new column =
var _max = maxx(filter(Table1, [UKC] =earlier([UKC]) && [Deposit chnage price on] <earlier([Deposit chnage price on])),[Deposit chnage price on])
return
countx(filter(Table2 , Table1[UKC] =Table2[UKC] && Table2[Deposit] <= Table1[Deposit chnage price on] && (isblank(_max) || Table2[Deposit] >=_max)),Table2[UKC] )

[kkalyanrr]

@amitchandak 
Thank you it worked, how ever when I try to interact with chart from Table 2 , the other chart which is from Table1 is not responsive..
Please help on fixing it.