Forum Discussion
Helper ICount for past 3 months
Hello,
Can somebody help me on this one.
I have two tables
Dispute Table
| Account No. | Account Name | Dispute Date |
| 200357736 | David Smith | 5/01/2022 |
Reference Table
| Account No. | Account Name | Ref Date |
| 200357736 | David Smith | 1/01/2022 |
| 200357736 | David Smith | 2/01/2022 |
| 200357736 | David Smith | 3/01/2022 |
| 200357736 | David Smith | 4/01/2022 |
| 200357736 | David Smith | 5/01/2022 |
I want to add a count column based on the number of times david smith appear on the reference table. However, criteria is i need the maximum of Dispute Date and then minimun of ref date(3 months only).
Result should be
| Account No. | Account Name | Dispute Date | Dispute Count |
| 200357736 | David Smith | 5/01/2022 | 3 |
Please advise. Thank you!
Regards,
Kevin
Hi,
Please check the below picture and the attached pbix file.
Dispute Count CC = VAR _maxdate = MAXX ( FILTER ( Dispute, Dispute[Account No.] = EARLIER ( Dispute[Account No.] ) ), Dispute[Dispute Date] ) VAR _threemonthago = MINX ( DATESINPERIOD ( 'Calendar'[Date], _maxdate, -3, MONTH ), 'Calendar'[Date] ) RETURN COUNTROWS ( FILTER ( RELATEDTABLE ( Reference ), Reference[Ref Date] >= _threemonthago && Reference[Ref Date] <= _maxdate ) )
5 Replies
- amitchandak
Super User
kevinedora , Try two measures like
max Date = calculate(max(Reference[Ref Date]), filter(allselected(Reference), Reference[Ref Date] = max(Reference[Ref Date]) ) )
Rolling 3 = //with date table
var _max = calculate(max(Reference[Ref Date]), filter(allselected(Reference), Reference[Ref Date] = max(Reference[Ref Date]) ) )
var _min = date(Year(_max), month(_max) -3, Day(_max))+1
BLANK())
return
CALCULATE(countrows(Reference) ,DATESBETWEEN('Date'[Date],_min1,_max1))not using date table in second
Rolling 3 =
var _max = calculate(max(Reference[Ref Date]), filter(allselected(Reference), Reference[Ref Date] = max(Reference[Ref Date]) ) )
var _min = date(Year(_max), month(_max) -3, Day(_max))+1
BLANK())
return
CALCULATE(countrows(Reference) ,filter(allselected(Reference[Ref Date]), Reference[Ref Date]>= _min && Reference[Ref Date]<= _max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos. - Jihwan_Kim
Hi,
Please check the below picture and the attached pbix file.
Dispute Count CC = VAR _maxdate = MAXX ( FILTER ( Dispute, Dispute[Account No.] = EARLIER ( Dispute[Account No.] ) ), Dispute[Dispute Date] ) VAR _threemonthago = MINX ( DATESINPERIOD ( 'Calendar'[Date], _maxdate, -3, MONTH ), 'Calendar'[Date] ) RETURN COUNTROWS ( FILTER ( RELATEDTABLE ( Reference ), Reference[Ref Date] >= _threemonthago && Reference[Ref Date] <= _maxdate ) )
kevinedoraHello,
Thanks for the quick response when I transfer it to my file an error exist.
Please advise.
- kevinedora
It alreadys works, my bad. I put it as a measure instead of calc column. Thank you so much!
- kevinedora
Jihwan_Kim hello, thanks for helping me on this. however, can I add another scenario? What if I need to return the latest value instead of counting the rows? Say, the latest value is TX101. I've tried the max function however, if there's a value that is higher than TX101 (e.g TX102) that's the value it gets which is wrong it should be TX101 associated with the latest date. Please advise. thank you!
Result:Account No. Account Name Dispute Date Latest Value within 3 months 200357736 David Smith 5/01/2022 TX101
