Sum of values by id and status in different months

[new_bi]

Hello! I was wondering if someone could help me with this problem:

Based on the following sample data:

I need to create a measure that returns the sum of the column "Value" for the current month (or the selected month) only taking into account the users that have an "A" status and, 3 months later, a "B" status.
I highlighted the rows that meet these conditions.

For example, the measure would return the value 3000 for september of 2021, as it is adding the values 1000 (id 1) and 2000 (id 2).
It would return 200 for november 2021, and 0 for the rest of the months.

Please let me know if you need additional information.

Thanks in advance!

[Anonymous]

Hi @new_bi ,

Still not  very clear, why it would return 200 for november 2021? 

Why not 500(id5 + id6)?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[new_bi]

Hello! It would return 200 because only id 5 has an A status in november and a B status in february (3 months later).

Thanks!

[johnt75]

Try creating a measure as

Sum of A then B values =
var AValues = CALCULATETABLE( VALUES('Table'[user_id]), 'Table'[status] = "A")
var BValues = CALCULATETABLE( VALUES('Table'[user_id]), DATEADD('Date'[Date], 3, MONTH), 'Table'[status] = "B")
return CALCULATE(SUM('Table'[Value]), INTERSECT(AValues, BValues) )

then putting that in a visual with the year month column from your date table

[new_bi]

Hello! This measure does seem to return the correct value (6400), however it cannot be filtered by date - that is, if I add it to a table or line chart with the year month column it returns blank. It does return 6000 if I filter it by the year 2021; for some reason it doesn't return the value for november (400).

Thank you very much!

[johnt75]

can you share a sample PBIX ?

[new_bi]

Since I can't attach a power bi file to this post, I'll have to upload it later.

This is a screenshot of the sample power bi file I'm working right now:

This is what happens when I add a month column to the table:

Thank you very much! I'll try to upload this sample file today.

[amitchandak]

@new_bi , Try like

MTD = CALCULATE(SUM(Table[Value]),DATESMTD('Date'[Date]), filter(Table, Table[Status] = "A") )

or

MTD = calculate(CALCULATE(SUM(Table[Value]), filter(Table, Table[Status] = "A") ),,DATESMTD('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[new_bi]

Hello, thanks for the quick answer! My issue is that I need to sum only the values that have a status "A" and, 3 months later, a status "B" (while keeping the same user_id). While the solution you posted helps me calculate the sum of values with an "A" status, it doesn't take into account this change in status.

Thanks!