Forum Discussion
Solved
count basis date
I have below data....there is another calendar table that has inactive relationship with Expiration date column...I want to count the items as per expiration date....so when I put calendar date in a ...
For that you couldn't rely just on relationships, as only one can be active at a time. You would need something like
Billed and Expired = VAR VisibleDates = VALUES ( 'Date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( 'Table' ), TREATAS ( VisibleDates, 'Table'[Expiry Date] ), TREATAS ( VisibleDates, 'Table'[Billing Date] ) ) RETURN Result
Try
Num expirations =
CALCULATE (
COUNTROWS ( 'Table' ),
USERELATIONSHIP ( 'Date'[Date], 'Table'[Expiration Date] )
)
Looks good...but what if I need the count of items that were billed and expired in the same month as the calender date table....
For that you couldn't rely just on relationships, as only one can be active at a time. You would need something like
Billed and Expired = VAR VisibleDates = VALUES ( 'Date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( 'Table' ), TREATAS ( VisibleDates, 'Table'[Expiry Date] ), TREATAS ( VisibleDates, 'Table'[Billing Date] ) ) RETURN ResultThanks johnt75
It worked but I had to use userrelationship to link it with expiration date....the data is showing correct in that case only...otherwise not sure how it is showing very very less numbers...anyways...this is a great help..thanks....got to learn a new thing today 🙂
