Forum Discussion

BlueBandit93's avatar
BlueBandit93
Frequent Visitor
3 years ago
Solved

Working with large data, dynamic filter to select value on specific date

Hi! I am hoping someone has an easier solution.   I have a list of about 8m unique "buy numbers" with their current value and some details about the buy (clients, owner, etc.) Then I have a list ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi BlueBandit93 ,

    You can follow the steps below to get it:

    1. Create a date dimension table and apply the [Date] field on the slicer

    2. Create a measure as below

    Measure =
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _amenddate =
    CALCULATE (
        MAX ( BuyHistory[AmendedDate] ),
        FILTER ( BuyHistory, BuyHistory[AmendedDate] <= _seldate )
    )
RETURN
    SUMX (
        FILTER (
            BuyHistory,
            BuyHistory[BuyDate] >= DATE ( 2023, 01, 01 )
                && BuyHistory[BuyDate] < _amenddate
                && [Value] <> 0
        ),
        [Value]
    )

     

    If the above one can't help you, please provide more raw data in your table  'Buy' and 'BuyHistory' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

BlueBandit93's avatar
BlueBandit93
Frequent Visitor
3 years ago

Thank you Rena!!
This seems to be working, still need to test it with the full population.

I did however need to change the formula to say = _amenddate (instead of < _amenddate)

 

Two questions if you don't mind a follow up?

 

1.  I noticed you used SUMX(..., FILTER(....)) where I used CALCULTE(SUMX(....)), what is the reason for this?

 

2.  The above measure provides the correct values on the 4 different dates.  However the columns are not adding correctly:


Is there another way of amending this than to create another measure as follows to correct the totals:

CORRECT DATE 1 = sumx(values(buyhistory[buyserial])),[Asset value date 1])

 

Thank you!