cancel
Showing results 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

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
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
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

Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

@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.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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.

Super User

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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.
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

Please give Kudos or Mark as a Solution!

Proud to give back to the community!
Thank You!

Announcements

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 Monthly Update - June 2024

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

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
Top Kudoed Authors