Forum Discussion
count basis date
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
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
Thanks 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 🙂
though I have another condition I got stuck on....what if I need the count of items which got billed as the visiable date but got expired 2 months ago of visible dates
You can manipulate the filters for billing date and expired date independently, e.g.
Billed and Expired = VAR VisibleDates = VALUES ( 'Date'[Date] ) VAR EarlierDates = DATEADD ( 'Date'[Date], -2, MONTH ) VAR Result = CALCULATE ( COUNTROWS ( 'Table' ), TREATAS ( EarlierDates, 'Table'[Expiry Date] ), TREATAS ( VisibleDates, 'Table'[Billing Date] ) ) RETURN ResultBasically, you build a table of dates for use as the billing date and a separate table with potentially different dates for use as the expired date.
If you are going to have a lot of these types of manipulations it might be worth creating a calculation group with different calculation items for each combination you want to be able to show.