cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

Data Modelling

Hi,

 

I'm trying to calculate all claims against a contract.

There are two tables contract & claims.

Contract have two columns to identify the contract i.e. Contract_No & AMC_No

In the claims table there is only Contract_No against which right AMC_No is required.

For e.g, AMC_NO "A" ranges from 1st januray to 10th januray.

A contract should have first two claims dated 2nd & 9th january i.e. the total amount is 300.

 

May be there is some way to put a "for" loop or something of that sort.

Any help is highly appreciated!!!!! 🙂

 

Please feel free to post any further doubt in the problem.

 

Regards,

Saurabh Kedia

 

Please refer both the screenshot below:

 

Contract_PBI.JPGClaim_PBI.JPG

1 ACCEPTED SOLUTION

Hi @saurabh_kedia_

 

Ok...lets add one more argument in FILTER.

Try this

 

Desired Output =
CALCULATE (
    FIRSTNONBLANK ( Contract[AMC_No], 1 ),
    FILTER (
        Contract,
        Claims[Claim_Date] >= Contract[Start_Date]
            && Claims[Claim_Date] <= Contract[End_Date]
            && Claims[Contract_No] = Contract[Contract_No]
    )
)
Regards,
Zubair


View solution in original post

4 REPLIES 4
Community Champion
Community Champion

Hi @saurabh_kedia_

 

Try this Column

 

Desired Output =
CALCULATE (
    FIRSTNONBLANK ( Contract[AMC_No], 1 ),
    FILTER (
        Contract,
        Claims[Claim_Date] >= Contract[Start_Date]
            && Claims[Claim_Date] <= Contract[End_Date]
    )
)
Regards,
Zubair


Hi Zubair,

 

Thanks for your input! 🙂

 

Actually there are many contracts, I mean in the sample which I posted only have one contract but there are many contracts with over lapping contract duration. I tried your solution..it works perfectly fine with one contract but there is some issue when there are multiple contracts.

 

Please feel free to raise any doubts on the dataset or the query.

 

I've attached the sample data set, please refer the screenshot below:

 

Contract_PBI.JPGClaim_PBI.JPG

 

Regards,

Saurabh Kedia

Hi @saurabh_kedia_

 

Ok...lets add one more argument in FILTER.

Try this

 

Desired Output =
CALCULATE (
    FIRSTNONBLANK ( Contract[AMC_No], 1 ),
    FILTER (
        Contract,
        Claims[Claim_Date] >= Contract[Start_Date]
            && Claims[Claim_Date] <= Contract[End_Date]
            && Claims[Contract_No] = Contract[Contract_No]
    )
)
Regards,
Zubair


View solution in original post

Hi Zubair,

 

Thanks a lot! 🙂

 

Highly appreciated.

 

Regards,

Saurabh Kedia

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors