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
tudorgo
Regular Visitor

IF DATE without Aggregating Function like SUM, MAX in order to compute cumulative amount using QTD

Hello community, it's my first post, I hope you can help me.

 

I have a date table, and two other tables, say table A and table B, with two columns each: Date and Amount.

 

I am looking for a formula that can help me use the amount from one table if the date is below a value, and the amount from the other if the date is above a certain value.

 

Something like myAmount=IF('Date'[Date] < myDate, SUM('A'[Amount]), SUM('B'[Amount])

 

For some reason, PowerBi does not let me evaluate this without aggregation, so I am using a MAX in front of each Date. This is a problem becasue I also need the cumulative sum of myAmount, something like: cumulativeMyAmount = CALCULATE(myAmount,DATESQTD('Date'[Date]). In other words, I need this formula to sum up the values in A before myDate and continue in summing up values in B after myDate. Any thoughts on how I can solve this?

 

Hope this is explained ok. Your help is much appreciated!

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
tudorgo
Regular Visitor

Hi guys, here is a link to a PBI containing sample data: https://storage.googleapis.com/website_cdn/Sample%20data%20cum%20IF.pbix

 

The Sales are correctly distributed each month, but the Cummulative Sales are not added up correctly. The expected number for February should be 331 (31 in Jan + 280 in Feb), but as you can see, I am getting 590.

 

Thanks,

Tudor

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur many thanks for the simple and elegant solution! Sometimes you can't see the forest because of all the trees, I was going deep down the rabbit hole with a lot more complicated stuff.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tudorgo
Regular Visitor

Guys, thanks a lot for the engagement. I will add the sample tables asap (@fhill and @Ashish_Mathur), and also try the solution proposed by @v-shex-msft.

Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

HI @tudorgo,

You can try to use the following measure formula if helps:

 

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date],
        "Dynamic Amount",
            IF (
                'Calendar'[Date] < currDate,
                CALCULATE (
                    SUM ( TableA[Amount] ),
                    FILTER ( ALLSELECTED ( TableA ), [Date] = EARLIER ( 'Calendar'[Date] ) )
                ),
                CALCULATE (
                    SUM ( TableB[Amount] ),
                    FILTER ( ALLSELECTED ( TableB ), [Date] = EARLIER ( 'Calendar'[Date] ) )
                )
            )
    )
RETURN
    SUMX (
        FILTER (
            summary,
            YEAR ( [Date] ) = YEAR ( currDate )
                && QUARTER ( [Date] ) = QUARTER ( currDate )
                && [Date] <= currDate
        ),
        [Dynamic Amount]
    )

 

If the above formula does not work, can you please share a pbix or some dummy data that keep the raw data structure to test?

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
fhill
Resident Rockstar
Resident Rockstar

Could you please mock up 3 simple data tables as you described above, and a sample output?  It doesn't sound like too hard an ask, but I'm just not fully following the logic as it searches the different tables?  Thank You




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors