Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Filtering column by Future date

Hello all,

I have a table coming from an SQL Query where one of the columns shows the date when a user registered and the next column shows how many purchases they made all time, something like this:

 

UsernameRegisteredDateTotalPurchases
username01/01/2250

 

Now, I want to have a new column Purchases20Days to show ONLY the purchases made 20 days after the registration date. In addition to this, this needs to be connected to a date filter where an admin can just select whatever registration date range, so the table will change to show the users that registered between that date range.

 

I'm thinking the only way to approach this would be via DAX, but after looking through the forums I just can't find the answer. I really appreciate your assistance!

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try a measure like

     

    new measure  =
    var _date = calculate([RegisteredDate], allexcept(Table, Table[Username])
    var _sum = calculate(Sum(Table[TotalPurchases]))
    return
    sumx(Values([Username]), calculate(_sum, filter(Table, Table[RegisteredDate] >=_date && [RegisteredDate] <=_date)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Anonymous,

    AFAIK, current power bi does not support creating dynamic clause column/table based on filter and slicer effect. They work on different levels and you can't use child level to affect its parent. I'd like to suggest you use a measure formula instead.

    If you are confused about the coding formula, please share some dummy data to test.

    How to Get Your Question Answered Quickly 

    Notice: the data level of power bi(from parent to child level)

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaxoin Sheng

  • mh2587's avatar
    mh2587
    Super User
    4 years ago
    NEXT 20 Days PURCHASE =
    var currentDate = LASTDATE('Date Table'[Date])
    var Next20Days = DATEADD(LASTDATE('Date Table'[Date]),20,DAY)
    var TPURCHASE = CALCULATE(SUM(TABLE NAME[TOTAL PURCHASE]),DATESBETWEEN('Date Table'[Date],Next20Days,currentDate))
    return TPURCHASE