Date between dates

[Anonymous]

Hello everyone,

I have been struggling with this for a while now, and don't know at all how to do what I want. 

I have two fact tables, A and B whose are both linked with a calendar and the agent ID frame of reference.

Table A contains data about employees absence ( a start and an end date). 

Table B contains data about expense account of each employee and the date of the expense.

I would like to build up a conditonal to show an expense made during an employee's absence. The goal would be that the formula find the ID, and its absences and expenses related to it. 

Is it possible to do so and how ? 

[lbendlin]

For each employee/agent create a concatenation of all their absences as CALENDAR() lists. lets say Agent P has two absences then you would union the two CALENDAR() tables into one. 

Then do a concatenation of all expense dates for Agent P into another table.

Finally do an INTERSECT() between the two tables, which gives you all the expense dates for Agent P that occurred during Agent P's absence.

Go Perry!

[Anonymous]

Thank you for your answer. Could you desribe the first step a bit more ? 

The step that I don't fully understand is the following :

"For each employee/agent create a concatenation of all their absences as CALENDAR() lists. lets say Agent P has two absences then you would union the two CALENDAR() tables into one. " 

How can I create a calendar for each of my employee's lines ? 

Does the concatenation you are talking about made by the Concatenate formula or is it juste a reference to what the Calendar one will do ? 

Thanks ! 

[lbendlin]

Here's how to create a date range (as a one column table)

https://docs.microsoft.com/en-us/dax/calendar-function-dax

and here's how to concatenate two or more of these created date ranges (which are tables)

https://docs.microsoft.com/en-us/dax/union-function-dax

Provide sample data if you want to see sample code.

[Anonymous]

I saw this before but tbh it dindn't helped me a lot ...

My data are from 2 different excel extraction that are like : 

Absences : 

Name	ID	Abs code	Detailled abs reason	Reason	Starting Date	End date	Month	Starting Hour	Ending Hour	Duration (D)
Siras Dorian	XXX	RTT	RTT	RTT	22/09/2020	23/09/2020	09	0	0	2
Siras Dorian	xxx	RTT	RTT	RTT	25/09/2020	25/09/2020	09	0	0	1

Expenses : 

Month	ID	Name	Surname	Complete Name	Type de carrière	Fonction	Niv	Company	Reason	Quantity	Date
September	XXX	Siras	Dorian	Siras Dorian	C	KJ4	4A	ZZZ	IEV	1	26/09/2020

---

[lbendlin]

I may have hit a roadblock with my idea to do this in DAX. This is how far I got

I have successfully created the two date ranges in the table variable c.  But now I need to find a way to UNION the "Cal"  values of the c table.  There seems to be nothing in DAX that allows me to handle tables inside tables.

This is something that is trivial to do in Power Query.  Would you be ok with a Power Query solution instead?

[Anonymous]

Yes something with PowerQuery would be fine, this is also ok for me 🙂 

[lbendlin]

Here's how to do it in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSixWcMkvykzMU9JRioiIAJJBISFopKW+kZG+kYGRAYRtDGMbWIIIKDZSitXBMLGiogKXiaZIJppiNdFQKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, #"Abs code" = _t, #"Detailled abs reason" = _t, Reason = _t, #"Starting Date" = _t, #"End date" = _t, Month = _t, #"Starting Hour" = _t, #"Ending Hour" = _t, #"Duration (D)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Starting Date", type date}, {"Duration (D)", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([Starting Date],[#"Duration (D)"],#duration(1,0,0,0))),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Date",{"Name", "ID", "Abs code", "Detailled abs reason", "Reason", "Date"})
in
    #"Removed Other Columns"

That will provide the individual absence day list for all users which you can then INTERSECT with the expense date lists for the same users.

Conflict = 
var n = SELECTEDVALUE(Absences[Name])
var c = SELECTCOLUMNS(FILTER(all(Absences),Absences[Name]=n),"Date",Absences[Date])
var e = SELECTCOLUMNS(FILTER(all(Expenses),Expenses[ Name]=n),"Date",Expenses[Date ])
return countrows(INTERSECT(c,e))

[Anonymous]

Thank you very much, I think that I an now starting to understand something. To make it easier I didn't told you all of the columns names beacuse I didn't fought it would have been useful. 

Below are my two  blank files, dates abs for the absences, and depenses for expenses

https://we.tl/t-LcqDdVUsYg

Would it be possible for you do redo the same Power Query code in M ? 

Thanks a lot 

Dorian