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
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
Result
Basically, 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.
If I use this measure...it says a table of multiple values was supplied where a single value was expected and giving error....I have tried it with Max and selectedvalue as well...but of no use...
Can you post the exact code you are using ?