Extract date (in the text format) from a list ( { .... , .... , .... } ) then count using DAX

[Hystericred]

Hi,

I'm new to the community : ) I'd like to know if you guys could help me on this.

I have created 2 tables 1) date table running from 1-Jan-2020 to 31-Jan-2020 2) table with 3 columns : ID, Start_Date , and End_Date > and extracted a date list between Start_Date and End_Date in power query using custom column formula {[Start_Date]..[End_Date]}. Let's call this below table "Example1"

ID	Start Date	End Date	Date List
1	43831 (this is 1-Jan-2020)	43840 (this is 10-Jan-2020)	43831:43832:43833:43834:43835:43836:43837:43838:43839:43840
2	43831 (this is 1-Jan-2020)	43834 (this is 4-Jan-2020)	43831:43832:43833:43834

I want to know if it's possible to write DAX to
1. create calculated culumn in the date table that able to see all the date value inside the lists under Date List column
2. then count for how many x date this calculated field function see running through the column and ignore to count the last item in the list

In this case, the resulut should be :

1-Jan-20 = 2
2-Jan-20 = 2
3-Jan-20 = 2
4-Jan-20 = 1
5-Jan-20 = 1
6-Jan-20 = 1
7-Jan-20 = 1
8-Jan-20 = 1
9-Jan-20 = 1
10-Jan-20 = 0

I have a feeling that making a measure or doing it in power query might be more appropriate than the calculated column - I'm not sure.

  

Thank you in advance.

Chadil.B

[v-deddai1-msft]

Hi Chadil.B,

According to your description, my understanding is that you want to create a calculated column to count rows that <Date List> contain 43831(

1-Jan-2020) If anything is misunderstood, please tell me.

Would you please try to use the following dax to create calculated column:

COUNT =

CALCULATE (

COUNTROWS ( 'Example1' ),

FILTER ( 'Example1', SEARCH ( 43831, 'Table'[Date List] ) <> BLANK () )

)

Best Regards,

Dedmon Dai

[Hystericred]

@v-deddai1-msft Hi Dedmon - Not sure about it. Please revisit the question - I have just edited - hope it is clearer.

[v-deddai1-msft · Accepted solution]

Hi Chadil.B,

Based on your requirements， I suggest you create a new datelist(datelist1 in my sample) column that does not contain enddate：

CALCULATE(COUNTROWS(Example1), FILTER( 'Example1',SEARCH('Date'[date], 'Example1'[datelist1],,0)>0))+0

 For more details, please refer to pbix :https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeWRSs3-k0pGtrX0gq...

Best Regards,

Dedmon Dai

[Hystericred]

@v-deddai1-msft thank you very much - allow me sometimes to test it, will get back to you for any updates.

[v-deddai1-msft]

any update?

[Greg_Deckler]

Maybe, what is the reference date for your date numerical values? For example, Unix is 1/1/1970. DAX is 12/30/1899. 

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/Desktop/Converting-UNIX-time-to-Date-in-PowerBI-for-Desktop/td-p/13...