Consider period between two columns in the slicer

[PBI_alma]

Hello Community,

I have an issue here that I really need some help with:

I have two tables, one containing the IDs that have a certain status in a certain time-period (red, green, blue). The time period is defined by a column startdate and a column enddate. In the other table, the revenue of the IDs at a certain point in time. Now, depending on a date filter (presumably via an extra date table), I need to know how much revenue was generated in the filtered period with the individual colour statuses:

The two tables are joined via a third one within the IDs, in this third table the IDs are unique.

If I set the date filter of the revenue to 01.02.23 - 31.03.23, I would like to see the results of the yellow cells:

red: 1.100

blue: 1.000

green: 900

Do you have any idea how to solve this?

Thank you and best regards,

Alma

[PBI_alma]

Hi BBF,

at first, thank you very much for your great support.

I had used the function you gave me and it worked fine, only I still generated wrong values because I didn't have a third table 'unique' with unique IDs between the first ones to link these two via [ID].

If I include this, the previous formula no longer works, as there is no longer a direct connection between the two columns in the Related function. Unfortunately, the attempt to write a second Related function behind it did not work either. how can I adjust the corresponding formula? A thousand thanks for your help.

Revenue by Status =
SUMX(
VALUES('ID Status'[Status]),
CALCULATE(
SUM('Revenue'[Revenue]),
FILTER(
'Revenue',
'Revenue'[Date] >= MIN('ID Status'[Start Date]) && 'Revenue'[Date] <= MAX('ID Status'[End Date]) &&
RELATED('ID Status'[ID]) = 'Revenue'[ID]
)
)

Best, Alma

[BeaBF]

@PBI_alma Try with:

Revenue by Status =
SUMX(
VALUES('ID Status'[Status]),
CALCULATE(
SUM('Revenue'[Revenue]),
FILTER(
'Revenue',
'Revenue'[Date] >= MIN('ID Status'[Start Date]) &&
'Revenue'[Date] <= MAX('ID Status'[End Date]) &&
RELATEDTABLE('Unique')[ID] = RELATED('ID Status'[ID]) &&
RELATED('Unique'[ID]) = 'Revenue'[ID]
)
)
)

In this formula, we use the RELATEDTABLE('Unique')[ID] expression to get the ID column from the third table, and compare it with the ID in the ID Status table using the RELATED function. We then use the second RELATED function to compare the ID from the Revenue table with the ID in the third table. This way, we can filter the Revenue table based on the ID Status table, even though there is no direct relationship between them.

BBF

[PBI_alma]

@BeaBF that works, thank you very much!

I still have the challenge that I have to insert a third table with unique IDs in between - do I then need two related functions? Can you help me here as well?

Thanks a million and best regards

Alma

[BeaBF]

@PBI_alma Great, I'm glad it works! 

Yes, you have to import the table and relate it via a unique ID, in this way you can get the information you need. If you have any detailed doubts, write to me.

BBF

[BeaBF · Accepted solution]

@PBI_alma  Hi!
Try this:

Revenue by Status =
SUMX(
VALUES('ID Status'[Status]),
CALCULATE(
SUM('Revenue'[Revenue]),
FILTER(
'Revenue',
'Revenue'[Date] >= MIN('ID Status'[Start Date]) && 'Revenue'[Date] <= MAX('ID Status'[End Date]) &&
RELATED('ID Status'[ID]) = 'Revenue'[ID]
)
)

BBF
)