count basis date

[sandeep_sharma]

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

[johnt75]

Try

Num expirations =
CALCULATE (
    COUNTROWS ( 'Table' ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[Expiration Date] )
)

[sandeep_sharma]

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....

[johnt75 · Accepted solution]

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

[sandeep_sharma]

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 🙂

[sandeep_sharma]

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

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Based on that data shared, show the expected result very clearly.

[johnt75]

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.

[sandeep_sharma]

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...

[johnt75]

Can you post the exact code you are using ?