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

[tudorgo]

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!

[tudorgo]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[tudorgo]

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

[Ashish_Mathur]

You are welcome.

[tudorgo]

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]

Hi,

Share some data and show the expected result.

[v-shex-msft]

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

[fhill]

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