Forum Discussion

zxmon21's avatar
zxmon21
New Member
3 years ago
Solved

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...
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

v-yadongf-msft's avatar
v-yadongf-msft
Icon for Community Support rankCommunity Support
3 years ago

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.