cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BlueBandit93
Frequent Visitor

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

Table: Buy

Buy numberValue
10
27
35
4etc

 

Table: BuyHistory

BuyNumberDate ChangedValue
115 Jan 20236
123 Jan 2023

12

130 Jan 2023

0

22 Feb 2023

7

325 Jan 2023

11

33 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 numberClientSlicer 1:
23 January
Slicer 2:
1 February
Slicer 3:
2 February
Slicer 4:
3 February
1Client A12000
2Client Adoes not exist = 0does not exist = 077
3Client Bdoes not exist = 011115
Total: 12111812

 

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!!!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
BlueBandit93
Frequent Visitor

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:

BlueBandit93_1-1686493646636.png


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])

 

BlueBandit93_3-1686494246165.png

Thank you!

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors