topic Return Data For 6 Months Within A Time Period in DAX Commands and Tips

Return Data For 6 Months Within A Time Period

Anonymous — Mon, 21 Feb 2022 17:29:43 GMT

Hi,

If I want to return true if a person has won a has within 6 months of their whole time working at a job, how would I code this in Dax?

So for example, if User A has won a prize on 04/07/2020 and another prize on 05/10/2020 and worked at the company from 01/01/2020 (Start date) to 15/12/2020 (End date), how would I set a date filter to find if the user has won two prizes within 6 months of their start date and end date?

User	Prize Date	Start Date	End Date	Prize
A	04/07/2020	01/01/2020	15/12/2020	1
A	05/10/2020	01/01/2020	15/12/2020	1

This is the example table. The Prize Count should show 1 after adding a measure using dax as User A has won two prizes in 6 months.

This is what I have so far:

(Users is the Table Name)

Prize Count = CALCULATE( SUM(Users[Prize])>1

When I create a table with User ID and Prize Count it shows me the correct count, but now I need to add that date filter to check between their dates of employment to see if in a 6 month period, they've had more than 1 prize.

Re: Return Data For 6 Months Within A Time Period

amitchandak — Tue, 22 Feb 2022 02:52:52 GMT

Anonymous , Try measure like

//Date1 is independent Date table
new measure Users Won =
var _max = maxx(allselected(Date1),Date1[Date]) // Or use Today
var _min = eomonth(_max, -6) +1
return
calculate( distinctCOUNT(Table[User]), filter('Table', 'Table'[Prize Date] >=_min && 'Table'[Prize Date] <=_max && Table[Start Date]<= _max && Table[End date] >= _min))

//Date1 is independent Date table
new measure Prize Won =
var _max = maxx(allselected(Date1),Date1[Date]) // Or use Today
var _min = eomonth(_max, -6) +1
return
calculate( COUNT(Table[User]), filter('Table', 'Table'[Prize Date] >=_min && 'Table'[Prize Date] <=_max && Table[Start Date]<= _max && Table[End date] >= _min))

Re: Return Data For 6 Months Within A Time Period

Anonymous — Thu, 24 Feb 2022 08:16:10 GMT

Hi Anonymous ,

15/12/2020 is definitely December 15th, so 04/07/2020 and 05/10/2020 are July 4th and October 5th, right?

If it is, it does not meet the condition you said to be awarded within six months(Start date is 1/1/2020, so max date is 7/1/2020). Then 04/07/2020 and 05/10/2020 will return false.

Please correct me if I'm wrong.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.