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

[BlueBandit93]

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 of each of the buy's history values

Table: Buy

Buy number	Value
1	0
2	7
3	5
4	etc

Table: BuyHistory

BuyNumber	Date Changed	Value
1	15 Jan 2023	6
1	23 Jan 2023	12
1	30 Jan 2023	0
2	2 Feb 2023	7
3	25 Jan 2023	11
3	3 Feb 2023	5

I need to build a report to show what buy's value was on specific dates (selected by 4 different date slicers) to get the below output:

Buy number	Client	Slicer 1: 23 January	Slicer 2: 1 February	Slicer 3: 2 February	Slicer 4: 3 February
1	Client A	12	0	0	0
2	Client A	does not exist = 0	does not exist = 0	7	7
3	Client B	does not exist = 0	11	11	5
Total:		12	11	18	12

At first I tried to write measures for each date, to pick up the value on the 4 slicer selected values, and it works for small filtered data, but proivdes an error "Visual has exceeded the available resources" when working over all the 8m buy numbers - which I understand.

My next thought was to use Generate and Summarize, to generate a table for eachbuy at the 4 slicer values and calculating the value using measures or calculated columns.

But this seems to take a long time to generate across the timeframe (about 12 years, which we can shorten to 4/5 years if needed)

Is there an easier way to do this that would allow me to obtain the values quickly and also be able to summarise the above table by client if needed?

The measure I used to write for Slicer 1 (BillRun 1) that works on a smaller scale of the data:

______________________________________________________________________
ValueSlicer1 = 

var BR1 = filter(BuyHistory,BuyHistory[AmendedDate] <= [BillRun 1])

var MaxValue = CALCULATE(max(BuyHistory[AmendedDate]),BR1)

RETURN

calculate(

    sumx(BuyHistory,Buyhistory[Value]),

    BuyHistory[AmendedDate]=MaxValue)

___________________________________________________________________________
The table I tried to generate:

BR1 = generate(

    filter(

        summarize(BuyHistory,BuyHistory[BuySerial],BuyHistory[BuyDate],BuyHistory[Value]),

        BuyHistory[BuyDate]>=date(2023,01,01) && BuyHistory[BuyDate]<[BillRun 1] && [Value]<>0 

        ),

    Filter(

        SUMMARIZE(Buy,Buy[Date]),

        Buy[Date]>date(2023,01,01))

)

Thank you in advance!!!

[BlueBandit93]

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!

[v-yiruan-msft]

Hi @BlueBandit93 ,

Thanks for your feedback. I think the problem may not be that you are using calculate(sumx(), it may be some other calculation that is not returning the correct result. You can break your formula into multiple steps to verify each step and see if you get the desired result at each step.

In addition, you can refer the following links to resolve the incorrect total values problem.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

[v-yiruan-msft · Accepted solution]

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