Sum of last 12 months based on two tables

[fer78]

Hello, new to Power Bi and trying to find a measure to acalculate the last 12 months for below scenario, thanks in advance

I have two tables

PO Query Report

CustomerID PO#   PO_QTY-BOM  Po Creation Date

8745             101      300                1/22/2020 12:00:00 AM

8745              102     200                5/15/2021 12:00:00 AM

8745              103     100                12/30/2020 12:00:00 AM

2343              104     500                6/18/2021 12:00:00 AM

8745              105     800                3/12/2018 12:00:00 AM

2343              106     100                1/14/2021 12:00:00 AM

Master Query 

CustomerID

8745

2343

Master query will have unique customer IDs, so I will like to bring the last sum of the last 12 months into the Master Query as a measure.

example the sum of the last 12 months for 8745 should be 300. and for customer ID 2343 500.

Any help is appreciated

[v-kkf-msft · Accepted solution]

Hi @fer78 ,

Try the following formula:

Measure = 
CALCULATE(
    SUM('PO Query Report'[PO_QTY-BOM]),
    'PO Query Report'[Po Creation Date] <= EOMONTH( TODAY(), -1 ) 
        && 'PO Query Report'[Po Creation Date] >= EOMONTH( TODAY(), -13 ) + 1
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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