Using selected value from one table in a calculation in another table

[Anonymous]

Hi,

I've been struggling with this task in pbi:

I have TABLE1 where I have START_DATE and END_DATE.

In TABLE2 I generated ALL_DATES for last 2 years till today.

I have a slicer with ALL_DATES where end user can select any date, based on that date I need to calculate something related to both START_DATE and END_DATE.

for example: I've selected 10-10-2022 and I would like to see the count of items from TABLE1 where:

START_DATE<ALL_DATES(selected) and END_DATE<=ALL_DATES(selected)

is it possible? I tried to get max of ALL_DATES but it gave me strange results (different value for each row in TABLE1).

btw, is it possible to create a parameter which can be used like a variable in filters or measures? 

I'm experienced in Tableau and if I had to do it in Tableau it would take me 2 minutes, now I got stuck.

[Ashish_Mathur]

Hi,

My preferred approach would be to create one row for each date between the start date and end date.  Then create a relationship between the Date column so create and the Date column of the Calendar Table.  To receive further help, share data (in a format the can be pasted in an MS Excel file).

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table1:

Table2:

Table2 = CALENDAR(DATE(2022,01,01),DATE(2023,2,6))

2. create a measure with below dax formula

Measure =
VAR min_date =
    MINX ( 'Table2', [Date] )
VAR max_date =
    MAXX ( 'Table2', [Date] )
VAR tmp =
    FILTER ( ALL ( Table1 ), [Start Date] >= min_date && [End Date] <= max_date )
RETURN
    SUMX ( tmp, [Sales] )

3. add a slicer with Table2 field, add a card visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! That's what I was looking for.

I was a bit weird to me why max/min doesn't work (should've used maxx/minx) and the way of applying Filter() wasn't intuitive. 

[amitchandak]

@Anonymous , You can get max and Min date like

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Start Date] <=_max && 'Table'[End Date] >=_max))

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM