Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
danielhough
Helper II
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?

Capture.PNG

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
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

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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? 

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.Capture33.PNG 

 

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 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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