Filtering Values based on date, in unrelated tables

[SKan]

Hello everyone!

I have 4 tables in power bi with the mentioned columns:
-Date (date)
-Project (Project, location, start date,end date)
-Sales Table (location, Amount, date columns)
-Location( Location)
The project table contains multiple projects for same location with different start and end dates.

Example:

Location 1- Project A, 01/01/202, 01/06/2020

Location1 -Project B,----

Location 2-  Project C, ----

Location 2- Project D,....
Date and Location table has one to many relaionship with both sales and project table. Project and sales table are not related directly but a relationship canbe created through location if needed. I want to know and visualize the change in Total Sales Amount(from sales Table), one year before and after each project in the project table.For example if a project ends on Jun ,1, 2020, I want to to know the difference between the total sales of July 2019 -June 2020, and total sales from July 2020-Jun2021. I need this for each project in the project table. How can I do that?

I usedthe following but DAX doesnt recognize Projects table for Related function, although it has one to many relationship with date.

Difference =

Var TotalSalesBeforePrj = CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL('Date'),
'Date'[Date] >= RELATED(Project[Finish Date]) - 365     &&       'Date'[Date] <= RELATED(Project[Finish Date]) ))

Var TotalSalesAfterPrj = CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL('Date'),
'Date'[Date] >= RELATED(Project[Finish Date])   &&   'Date'[Date] <= RELATED(Project[Finish Date]) + 365))

Return TotalSalesAfterPrj - TotalSalesBeforePrj

TIA

[SKan]

the link should work now

https://drive.google.com/file/d/1zN_snC1qFotYPVpotjHRCJSK-YFUipgS/view?usp=sharing

[lbendlin]

Your sample data is not very conclusive. Note that I changed the data model and removed the CALENDARAUTO.

[SKan]

Thanks for the reply.

I am attaching the link to my problem using a sample data set.

We have data for a gym. There are three imported tables:

1-   Customer Info ( Customer Name, Address, Phone) : Contains Customer info 

2-   WeightRecords (Customer Name, Record Date, Weight) : Contains monthly weight record of each customer

3-   DietPlans (Customer Name,  DietPlanStartdate, DietPlanEndDate): Contains info of any diet plan a customer follows

A date table is craeted inside pbix file using Calendar function.

Relationships:

Date: WeightRecords (1:many)

Customer: WeightRecords (1:many)

Customer: DietPlans (1:many)

Date: DietPlans (1:many)

Customer follow different diet plans, and we want to know their average weight six months before the DietPlanEnddate, and average weight six months afterwards. For example, if the customer completed a diet plan on July 7, 2021, i want to know their avg weight for Jan to June 2021, and avg weight for Jul to Dec 2021. Then calculate the difference. I want it to be displayed as a table visual as follows:

Here is the link to the pbix file: 

https://drive.google.com/file/d/1zN_snC1qFotYPVpotjHRCJSK-YFUipgS/view?usp=drive_link

I have been trying  to do it by calculated column and measure, but the intellisense does not recognize DietPlans table for filter condition.

Avg weight before=  

calculate(AVERAGE(WeightRecords[Weight],

              Filter(WeightRecords, WeightRecords[Record Date]=DietPlans[DietPlanEndDate] &&              WeightRecords[Customer]=DietPlans[Customer]))

I have tried filtering the WeightRecords table with Date table, but it doesnt recognize it either. 

=CALCULATE((AVERAGE(WeightRecords[Weight]),

  Filter('Date', 'Date'[Date]>=DietPlans[DietPlanEndDate]     &&   

 'Date'[Date]<=DietPlans[DietPlanEndDate]- 180                  &&

DietPlans[Customer]=WeightRecords[Customer])))

Any help is appreciated. Please keep in mind that are other tables in the data model for RunningPlan (like diet plan) and RunningTimeRecord (like weight Records). So the Customer and Date table will have relationships with these tables too.

Thank you.

[lbendlin]

please check the link, it asks for access

[lbendlin]

You don't use RELATED in measures.  Read about TREATAS.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...