Two conditions on date field

[Anonymous]

Hi ,

I have a two date fileds from two tables . whenever i sort a date range say 01 Jan 2019 to 31 Dec 2019 from Date  it should count the records for previous all date fields before 01 Jan 2019 in Table (Date1)

In the same way it should sort records for 01 Jan 2019 to 31 Dec 2019 in another table (Date2).so that both conditions should be applied in a single dax.

Thanks in advance

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest writing a measure and create a variable to extract and stored the current date, then use this variable to calculate with two table records with 'ALL' function to ignore original filter effects.
If you confused about coding formula, please share some dummy data with minimum data structure for test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft 

@amitchandakprovided the link that helped me 

Anyhow Thank you 

[v-shex-msft]

HI @Anonymous,

Please share some dummy data with minimum data structure than we can test and coding formula on them.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , you should consider a common date dimension. Can you explain the need with a better example?

[Anonymous]

Yes @amitchandak 

I have two date fields in a table 

condition 1 : I should sort the records all previous date fields (suppose my search is 01 jan 2019 to 31 Dec 2019, it should count all the records before 01 Jan 2019 -------Date1 column

condition 2 : I should sort the records between 01 Jan 2019 to 31 Dec 2019 from Date 2 

Result : Count will be sum of condition 1 and condiotion 2

Thank you for the quick reply @amitchandak 

[amitchandak]

@Anonymous , Not very clear. Do you have data across to ranges : https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

@MFelix , @TomMartens , can you help.

[Anonymous]

Hi @amitchandak ,

I have used a DAX which i used for two date column conditions as below which is working fine .but ity was mentioned for last 1 year but i need the same logic to change dynamically whenever i search in a date range slicer .

example :  I have a data upto 2019 .  if i search from 1 jan 2019 to 31 Dec 2019 The below dax is working as my requirement 
If i use from 1 Jan 2018 to 31 Dec 2018 I am not getting the required value because the dax has been provided for last 1 year period from today .But, I need as that particular date range so please help me .

Last Period Employee =

var _min_date = minx(all('Calendar'),'Calendar'[Date])

var _Expression=if(ISFILTERED('CALENDAR'[Month Year]),maxx('CALENDAR',ENDOFMONTH(DATEADD('CALENDAR'[Date],-1,MONTH))),maxx('CALENDAR',DATEADD('CALENDAR'[Date],-1,YEAR)))

Return

CALCULATE(COUNTx(FILTER(Separation,Separation[Date of joining]<=_Expression && Separation[Date of joining]>=_min_date && (ISBLANK(Separation[Exit Date]) || Separation[Exit Date]>_Expression)),(Separation[Employee ID])),CROSSFILTER(Separation[Date of joining],'CALENDAR'[Date],None))

[Anonymous]

Hi @amitchandak 

I have two date fields Date 1 column and Date 2 column 
Example : if i select 2019 it should count 2018,2017,2016 and so on for Date 1 column (say count = 10)
similarly if i select 2019 it should count only 2019 records ( say count = 4)

Date 1 : I need prior records 
Date 2 : I need search records 
Result : 10+4 = 14

Thanks in advance 

[amitchandak]

@Anonymous 

Join both of them with the same date table, one active one inactive. Use userelation to select the date required. Change >= or <= as per need

new meaure = 
 var _max = maxx('Date','Date'[Date])
 return
 CALCULATE(COUNT(Table1[Table1 Id ]), filter(all('Date'), 'Date'[Date]<=_max),USERELATIONSHIP(Table1[Date1],'Date'[Date]) )+
 CALCULATE(COUNT(Table1[Table1 Id ]), filter(all('Date'), 'Date'[Date]>=_max),USERELATIONSHIP(Table1[Date2],'Date'[Date]))
 

Refer , step in

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi @amitchandak ,

The link provided helped me Thank you so much for the response in less time .

[Anonymous]

I have taken prior count for date 1 and count for date 2 and i added both but getting wrong because here the logic is date 2 fields get sorted based on date 1 

Thank you 

waiting for the reply