Email opening vs email sent, on a line chart

[mathician]

I have 1 table with the data under. And i want to create a line chart that shows, date and how much sent vs opened. Both of them have their own datetime. So i want to se how much that where sent today vs opened. If i select one of the date it gets messy, it will group by the days that it was sent.

Sendt	Sendt_dt	Opened	Opened_dt
1	2015-03-06 11:49:57.750	1	2015-04-06 11:49:57.750

[amitchandak]

@mathician ,

send Date = [Sendt_dt].date
Open Date = [Opened_dt].date

Join both these dates with date table and one will inactive realtion. Use the inactive one in measure using userelation

Example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Greg_Deckler]

@mathician Seems like you would need a disconnected date table and then you could do something like:

Measure Sent = 
  VAR __Date = MAX('Date'[Date])
RETURN
 COUNTROWS('Table',DATE(YEAR('Table'[SendtSendt_dt]),MONTH('Table'[SendtSendt_dt]),DAY('Table'[SendtSendt_dt])) = __Date)

Measure Opened = 
  VAR __Date = MAX('Date'[Date])
RETURN
 COUNTROWS('Table',DATE(YEAR('Table'[OpenedOpened_dt]),MONTH('Table'[OpenedOpened_dt]),DAY('Table'[OpenedOpened_dt])) = __Date)

[mathician]

How do i use my date coloumns, since powerbi just lets me use measure in this situation. Just i explained above.

[mathician]

Dosent seem like to work. When i add my table name and column name in "the place that i marked red" only measure coulmns apear, i dont get the options to add my date coulmns.

MAX('Date'[Date]) RETURN COUNTROWS('Table',DATE(YEAR('Table'[SendtSendt_dt]),

[v-lili6-msft]

hi  @mathician 

Just miss a "FILTER" in the formula

Measure Sent = 
  VAR __Date = MAX('Date'[Date])
RETURN
 COUNTROWS(FILTER('Table',DATE(YEAR('Table'[SendtSendt_dt]),MONTH('Table'[SendtSendt_dt]),DAY('Table'[SendtSendt_dt])) = __Date))

Measure Opened = 
  VAR __Date = MAX('Date'[Date])
RETURN
 COUNTROWS(FILTER('Table',DATE(YEAR('Table'[OpenedOpened_dt]),MONTH('Table'[OpenedOpened_dt]),DAY('Table'[OpenedOpened_dt])) = __Date))

Measure Opened =
VAR __Date = MAX('Date'[Date])
RETURN
COUNTROWS(FILTER('Table',DATE(YEAR('Table'[OpenedOpened_dt]),MONTH('Table'[OpenedOpened_dt]),DAY('Table'[OpenedOpened_dt])) = __Date))

Regards,

Lin

[mathician]

My date table is a normal CalenderAuto table. But my fact table has the value bellow "2020-09-09 22:32:57.000"

What coulmns should my datetable have? for it to be joinable. Because its not joining right now.

[v-lili6-msft]

hi  @mathician 

This work on my side, here is sample pbix file, please try it.

and could you please share your sample pbix file and expected output, that will be a great help.

Regards,

Lin

[mathician]

Returns Null on both values.

[vanessafvg]

what are you struggling with exactly?
in time analysis you need to create a date table. You can do this with calendarauto() or similar (as long as you dont have odd dates like 2999-99-99 otherwise it will create large tables.
you need to either create a role playing dimension or use both active and inactive relationships.

i would create a date table
link both of your dates to that table date
the relatioship with a dotted line is your inactive relationship, you can use the function userelationship() to get the measures with that date.

this will give yo umore information https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

https://www.youtube.com/watch?v=u8_aeLmXbVk