Row level date range

Hi my message was marked as spam and is just getting posted, so I'm bringing it back to the top for your attention. Thank you 🙂

I'd love to share the PBIX file, but not sure of the best way.

Hi, Sibrulotte 

You can try the following method to solve the problem.

Steps:
1.Three consecutive Merges

2. Add costom column to calcute the date differ between [Transactional.date_report] and [Send Log.Sent_date]

3. Filter Date differ if the date differ between 0 and 14

4. Remove unneeded columns

5. Back to desktop, use the following measure.

transaction_brut_result = 
CALCULATE(SUM(ResultTable[Transactional.transaction_brut]))

Advanced Editor:

let
    Source = Table.NestedJoin(Message, {"Message ID"}, #"Send Log", {"Message ID"}, "Send Log", JoinKind.RightOuter),
    #"Expanded Send Log" = Table.ExpandTableColumn(Source, "Send Log", {"Message ID", "Sent_date", "Contact ID"}, {"Send Log.Message ID", "Send Log.Sent_date", "Send Log.Contact ID"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Send Log", {"Send Log.Contact ID"}, Contact, {"Contact ID"}, "Contact", JoinKind.LeftOuter),
    #"Expanded Contact" = Table.ExpandTableColumn(#"Merged Queries", "Contact", {"Contact ID", "Epargnant id"}, {"Contact.Contact ID", "Contact.Epargnant id"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Contact", {"Contact.Epargnant id"}, Transactional, {"Epargnant_id"}, "Transactional", JoinKind.LeftOuter),
    #"Expanded Transactional" = Table.ExpandTableColumn(#"Merged Queries1", "Transactional", {"Epargnant_id", "date_report", "transaction_brut", "ind_adhesion"}, {"Transactional.Epargnant_id", "Transactional.date_report", "Transactional.transaction_brut", "Transactional.ind_adhesion"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Transactional", "Custom", each [Transactional.date_report] - [Send Log.Sent_date]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] >= #duration(0, 0, 0, 0) and [Custom] <= #duration(14, 0, 0, 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Message ID", "Send Log.Message ID", "Send Log.Sent_date", "Send Log.Contact ID", "Contact.Contact ID", "Contact.Epargnant id", "Transactional.Epargnant_id", "Transactional.date_report", "Transactional.ind_adhesion", "Custom"})
in
    #"Removed Columns"

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum