Forum Discussion
Counting rows with dates before [any date from a date-table]
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...
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.
2 Replies
- v-yadongf-msftCommunity Support
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.