Counting rows with dates before [any date from a date-table]

[zxmon21]

Hi,

I'm trying to find the number of items that we've purchased before any given time.

I'm starting with a table with one row per item and purchase date (simplified, there are a LOT more columns in the actual table):

I need to pivot "how many items were purchased before a given date", and put that on a timeline (that will extend over the dates on the datetable). I need to be able to apply pivot filters and row/columns to it, e.g. separate by Type.

The datetable is related to my PurchDate 

My DateTbl contains a measure:

InkDat2:=CALCULATE(COUNT(Inventarier[Type]);FILTER(Inventarier;max(DateTbl[Date])<=Inventarier[PurchDate]))

As you probably notice this will not work, as the relation filters out any Inventories with PurchDate < Date.

Actual result:

Desired result:

Surely there are other people who needed to do something like this? I can't think of a good search term...

[v-yadongf-msft · Accepted solution]

Hi @zxmon21 ,

Please try following DAX:

Measure = IF(
    ISINSCOPE('Table'[Type]),
    CALCULATE(COUNT('Table'[Type]),FILTER(ALL('Table'),'Table'[Type] = SELECTEDVALUE('Table'[Type]) && 'Table'[PurchDate] <= SELECTEDVALUE('Date'[PurchDate]))),
    CALCULATE(COUNT('Table'[Type]),FILTER(ALL('Table'),'Table'[PurchDate] <= SELECTEDVALUE('Date'[PurchDate])))
)

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[davehus]

Hi @zxmon21 ,

Would a date filter in the filter pane not achieve the same result without the need for a measure?

Did I help you today? Please accept my solution and hit the Kudos button.