Forum Discussion
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 table.....it should give the count of Items which have expiration date between the first and last day of that month......so for example in the table....it should give me the count of items with expiration date of Jun 2024 in front of Jun 2024 line item...can you suggest a measure
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
9 Replies
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 Result
