Working With Dates - Un Related Dataset

Question

&nbsp;Hi there,&nbsp;(Updated File Here:&nbsp;https://we.tl/t-Fzow7DHead)&nbsp;Here is my problem... I will keep it simple as I am trying to understand the concept .... I have a dataset that has (End Of Month Report Period), (Lease Start Date), (Lease End Date) and (Suburb). The data is structured such that you can have multiple properties for the (End Of Month Report Period).&nbsp;&nbsp;So I have loaded the csv file, once as is and the second time only selecting the&nbsp;End Of Month Report Period, renamed to (EOMPRT)&nbsp; and eliminating all duplicates. So I get two datasets, ALLDATA and EOMRPT.&nbsp;I setup three visuals ... One with ALLDATA in Table, The Slicer for EOMRPT in list form, I then have a card that Displayes the Selected EOMRPT ... this is a new measure&nbsp;NM-SEOMRPT = SELECTEDVALUE(EOMRPT[EOMRPT])&nbsp;Now the tables are not related ... I want to be able to claculate the following measures:&nbsp;&nbsp;NM_Lease_Start_Date_As_At_EOMRPT =IF (ALLDATA[Lease Start Date] &lt; [NM-SEOMRPT],1,0)&nbsp;NM_Lease_End_Date_As_At_EOMRPT =IF (ALLDATA[Lease End Date] &lt; [NM-SEOMRPT],1,0)&nbsp;I get an error in Dax?&nbsp;I tried using (Min(ALLDATA[Lease End Date]) but the calculation is incorrect ...&nbsp;&nbsp;Please advise as to what I am doing wrong ...?&nbsp;&nbsp;&nbsp;EOMRPT31/07/202231/08/202230/09/202230/11/202231/01/202328/02/202331/03/202330/04/202331/05/202330/06/202331/07/202331/08/202330/09/2023&nbsp; ALLDATAEnd of Month Report PeriodLease Start DateLease End DateSuburb31/07/2022 0:0010/07/2022 0:0010/01/2023 0:00BUNDALL30/11/2022 0:0022/11/2022 0:0022/05/2023 0:00ROCKHAMPTON CITY31/01/2023 0:005/01/2023 0:005/07/2023 0:00BEENLEIGH31/07/2022 0:007/07/2022 0:007/07/2023 0:00SURFERS PARADISE31/08/2022 0:002/08/2022 0:002/08/2023 0:00SPRING HILL31/08/2022 0:005/08/2022 0:005/08/2023 0:00NEW FARM30/09/2022 0:001/09/2022 0:001/09/2023 0:00NORTH WARD31/03/2023 0:001/03/2023 0:001/09/2023 0:00CAPALABA31/03/2023 0:002/03/2023 0:002/09/2023 0:00CHERMSIDE31/03/2023 0:004/03/2023 0:004/09/2023 0:00CHERMSIDE WEST30/09/2022 0:009/09/2022 0:009/09/2023 0:00SMITHFIELD30/09/2022 0:0015/09/2022 0:0015/09/2023 0:00RAILWAY ESTATE31/05/2023 0:005/05/2023 0:005/11/2023 0:00LEICHHARDT30/06/2023 0:001/06/2023 0:001/12/2023 0:00BEENLEIGH30/06/2023 0:002/06/2023 0:002/12/2023 0:00LEICHHARDT31/07/2023 0:003/07/2023 0:003/01/2024 0:00INALA31/01/2023 0:004/01/2023 0:004/01/2024 0:00WOODEND31/07/2023 0:004/07/2023 0:004/01/2024 0:00RAILWAY ESTATE31/01/2023 0:0010/01/2023 0:0010/01/2024 0:00SURFERS PARADISE31/08/2023 0:001/08/2023 0:001/02/2024 0:00NORTH WARD31/08/2023 0:002/08/2023 0:002/02/2024 0:00RAILWAY ESTATE30/09/2023 0:001/09/2023 0:001/03/2024 0:00RAILWAY ESTATE31/03/2023 0:005/03/2023 0:005/03/2024 0:00CAPALABA30/04/2023 0:001/04/2023 0:001/04/2024 0:00INALA30/04/2023 0:001/04/2023 0:001/04/2024 0:00LEICHHARDT31/05/2023 0:002/05/2023 0:002/05/2024 0:00SURFERS PARADISE31/05/2023 0:004/05/2023 0:004/05/2024 0:00BEENLEIGH30/11/2022 0:0011/11/2022 0:0011/05/2024 0:00RAILWAY ESTATE31/07/2023 0:002/07/2023 0:002/07/2024 0:00CAPALABA28/02/2023 0:002/02/2023 0:002/08/2024 0:00BEENLEIGH28/02/2023 0:004/02/2023 0:004/08/2024 0:00LEICHHARDT31/07/2022 0:005/07/2022 0:001/07/2025 0:00SURFERS PARADISE&nbsp;

amitchandak · Answer

Alice_Cooper , check how date table is used in the attached file to create measure or the given blogs 
&nbsp;
Between Dates - Dates between Measure wayPower BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzMhttps://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

alice_cooper · Answer

Thank you but this does not quite asnswer my questions... I have multiple End Of Moth Report Periods ... so does not quite work ...

Forum Discussion

Working With Dates - Un Related Dataset

2 Replies

Related content

Filtercontext with date relation

RELATED function not working

Working with two datasets

Refresh dataset api

Related Dax not working

ALLDATA
End of Month Report Period	Lease Start Date	Lease End Date	Suburb
31/07/2022 0:00	10/07/2022 0:00	10/01/2023 0:00	BUNDALL
30/11/2022 0:00	22/11/2022 0:00	22/05/2023 0:00	ROCKHAMPTON CITY
31/01/2023 0:00	5/01/2023 0:00	5/07/2023 0:00	BEENLEIGH
31/07/2022 0:00	7/07/2022 0:00	7/07/2023 0:00	SURFERS PARADISE
31/08/2022 0:00	2/08/2022 0:00	2/08/2023 0:00	SPRING HILL
31/08/2022 0:00	5/08/2022 0:00	5/08/2023 0:00	NEW FARM
30/09/2022 0:00	1/09/2022 0:00	1/09/2023 0:00	NORTH WARD
31/03/2023 0:00	1/03/2023 0:00	1/09/2023 0:00	CAPALABA
31/03/2023 0:00	2/03/2023 0:00	2/09/2023 0:00	CHERMSIDE
31/03/2023 0:00	4/03/2023 0:00	4/09/2023 0:00	CHERMSIDE WEST
30/09/2022 0:00	9/09/2022 0:00	9/09/2023 0:00	SMITHFIELD
30/09/2022 0:00	15/09/2022 0:00	15/09/2023 0:00	RAILWAY ESTATE
31/05/2023 0:00	5/05/2023 0:00	5/11/2023 0:00	LEICHHARDT
30/06/2023 0:00	1/06/2023 0:00	1/12/2023 0:00	BEENLEIGH
30/06/2023 0:00	2/06/2023 0:00	2/12/2023 0:00	LEICHHARDT
31/07/2023 0:00	3/07/2023 0:00	3/01/2024 0:00	INALA
31/01/2023 0:00	4/01/2023 0:00	4/01/2024 0:00	WOODEND
31/07/2023 0:00	4/07/2023 0:00	4/01/2024 0:00	RAILWAY ESTATE
31/01/2023 0:00	10/01/2023 0:00	10/01/2024 0:00	SURFERS PARADISE
31/08/2023 0:00	1/08/2023 0:00	1/02/2024 0:00	NORTH WARD
31/08/2023 0:00	2/08/2023 0:00	2/02/2024 0:00	RAILWAY ESTATE
30/09/2023 0:00	1/09/2023 0:00	1/03/2024 0:00	RAILWAY ESTATE
31/03/2023 0:00	5/03/2023 0:00	5/03/2024 0:00	CAPALABA
30/04/2023 0:00	1/04/2023 0:00	1/04/2024 0:00	INALA
30/04/2023 0:00	1/04/2023 0:00	1/04/2024 0:00	LEICHHARDT
31/05/2023 0:00	2/05/2023 0:00	2/05/2024 0:00	SURFERS PARADISE
31/05/2023 0:00	4/05/2023 0:00	4/05/2024 0:00	BEENLEIGH
30/11/2022 0:00	11/11/2022 0:00	11/05/2024 0:00	RAILWAY ESTATE
31/07/2023 0:00	2/07/2023 0:00	2/07/2024 0:00	CAPALABA
28/02/2023 0:00	2/02/2023 0:00	2/08/2024 0:00	BEENLEIGH
28/02/2023 0:00	4/02/2023 0:00	4/08/2024 0:00	LEICHHARDT
31/07/2022 0:00	5/07/2022 0:00	1/07/2025 0:00	SURFERS PARADISE