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 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-msft's avatar
    v-yadongf-msft
    Community 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.

  • davehus's avatar
    davehus
    Memorable Member

    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.