cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Calculate, Sum, and Filters not returning the right result?

Hey Everyone, I have a calulated date table, as seen here:

``````1. DAILY_TABLE_MMMF =
Var Cal =
CALENDAR(DATE(YEAR(TODAY())-1,1,1), MAX(PS_MMC_MMMF_RPT_VW[TRANSACTION_DT]))
Var Des =
SUMMARIZE(PS_MMC_MMMF_RPT_VW,'PS_MMC_MMMF_RPT_VW'[DESCR1],'PS_MMC_MMMF_RPT_VW'[DESCR],PS_MMC_MMMF_RPT_VW[FLOATING_MKT_CD_CCY],PS_MMC_MMMF_RPT_VW[TRANSACTION_ISSUER], 'PS_MMC_MMMF_RPT_VW'[MMC_INV_POOL_ACCT])
VAR TAB = GENERATE(Cal,Des)
Return
TAB``````

I then have a calulated column as seen here:

``````Daily Beginning Balence = CALCULATE(
sum(
'PS_MMC_MMMF_RPT_VW'[TRANSACTION_AMT]),
Filter('PS_MMC_MMMF_RPT_VW','PS_MMC_MMMF_RPT_VW'[FLOATING_MKT_CD_CCY]=='1. DAILY_TABLE_MMMF'[FLOATING_MKT_CD_CCY]),
Filter('PS_MMC_MMMF_RPT_VW','PS_MMC_MMMF_RPT_VW'[TRANSACT_CURRENCY]=='1. DAILY_TABLE_MMMF'[TRANSACT_CURRENCY]),
FILTER('PS_MMC_MMMF_RPT_VW','PS_MMC_MMMF_RPT_VW'[TRANSACTION_DT]<='1. DAILY_TABLE_MMMF'[Date]))``````

The issue that I am having is, the results are not aligning properly, the data is appearing on the incorrect rows, or not appearing at all?

1 ACCEPTED SOLUTION
Helper II

I found the issue! Like I have experienced many times before in life, my model had a bad relationship lol

4 REPLIES 4
Super User

@danielhough Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

I found the issue! Like I have experienced many times before in life, my model had a bad relationship lol

Helper II

Hey Greg, so I parsed out the data and created a new PBI and the formulas all worked.

Is there a computational or data limit within desktop that would prevent this calculation from completing?

Helper II

Thank you Greg for responding! So in words what I am trying to do is, to create a daily table that summarizes from the beginning of time to today, so a rolling total. This is a test bit of the source data and at the bottom should be the result.

I have a date table created where I created a daily table since the minimum of my source data and maximim to the source data. This creates a daily row for each item in the source data, by Item i mean DESCR1, there are multiple in the full source data.

But the issue I am seeing is, the DAX is not aliginging the date and summation properly. Its putting a GBP line on a USD line. My DAX is trying to sum and filter by items in each row to place the value in the right row and date.

Does this help? I can explain much more if needed

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors