topic Compare multiple date fields to a value that can be selected manually using DAX in DAX Commands and Tips

Compare multiple date fields to a value that can be selected manually using DAX

Anonymous — Fri, 11 Nov 2022 16:53:31 GMT

Hi,

I am trying to create a table with some monthly ratios which use different dates from my dataset and I do not know if that is possible. A normal parameter does not seem to do the trick and I want to know if I could have a dropdown where I can select a month and a year or the days within a month in two variables to compare them against the dates used for the ratios.

Opportunity	Request Date	Process Date	Close date
1	01/06/2022	10/10/2022	25/10/2022
2	20/06/2022	30/06/2022	01/08/2022
3	05/08/2021	21/09/2021	01/01/2022
4	21/04/2020	06/06/2020	12/12/2020
5	08/10/2022	12/10/2022	21/10/2022
6	14/06/2021	24/07/2021	02/03/2022
7	23/01/2022	12/06/2022	24/06/2022
8	25/04/2021	05/05/2021	12/01/2022

As you can see above, there are multiple dates and the ratios I want to calculate vary between process booked date and close date.

I would like to know if I can somehow store a specific month, say 03/2021 and see how many opportunities have been processed before that date and how many opportunities have been closed afterwards by implemeting this parameter in a DAX function.

https://www.youtube.com/watch?v=SMHbMhX1Ggg&ab_channel=DhruvinShah

This video seems helpful in terms of how to structure the DAX formula, but my goal is to do something similar but instead of typing the date every time, I can just tie it to a paramater that my team can change and adapt the formulas as they are tied to the said parameter.

Thank you in advance

Re: Compare multiple date fields to a value that can be selected manually using DAX

amitchandak — Sat, 12 Nov 2022 02:25:39 GMT

Anonymous , My HR blog can help

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

Also with an independent date table, you can have measures like

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', eomonth('Table'[Request date],0) = eomonth(_max,0) ))

Re: Compare multiple date fields to a value that can be selected manually using DAX

Anonymous — Mon, 14 Nov 2022 02:40:26 GMT

Hi Anonymous ,

Here are the steps you can follow：

1. Create measure.

Count Process Date = VAR _selectyear = SELECTEDVALUE ( 'Table 2'[Date].[Year] ) VAR _selectmonth = SELECTEDVALUE ( 'Table 2'[Date].[Month] ) VAR _selectday = SELECTEDVALUE ( 'Table 2'[Date].[Day] ) VAR _selectnumber = MAXX ( FILTER ( ALL ( 'Table 2' ), FORMAT ( 'Table 2'[Date], "mmmm" ) = _selectmonth ), MONTH ( 'Table 2'[Date] ) ) RETURN SWITCH ( TRUE (), HASONEVALUE ( 'Table 2'[Date].[Year] ) && HASONEVALUE ( 'Table 2'[Date].[Month] ) && HASONEVALUE ( 'Table 2'[Date].[Day] ), COUNTX ( FILTER ( ALL ( 'Table' ), 'Table'[Process Date] <= DATE ( _selectyear, _selectnumber, _selectday ) ), [Opportunity] ), HASONEVALUE ( 'Table 2'[Date].[Year] ) && HASONEVALUE ( 'Table 2'[Date].[Month] ), COUNTX ( FILTER ( ALL ( 'Table' ), 'Table'[Process Date] <= DATE ( _selectyear, _selectnumber, 1 ) ), [Opportunity] ), HASONEVALUE ( 'Table 2'[Date].[Year] ), COUNTX ( FILTER ( ALL ( 'Table' ), YEAR ( 'Table'[Process Date] ) <= _selectyear ), [Opportunity] ), 0 )

Count Close Date = VAR _selectyear = SELECTEDVALUE ( 'Table 2'[Date].[Year] ) VAR _selectmonth = SELECTEDVALUE ( 'Table 2'[Date].[Month] ) VAR _selectday = SELECTEDVALUE ( 'Table 2'[Date].[Day] ) VAR _selectnumber = MAXX ( FILTER ( ALL ( 'Table 2' ), FORMAT ( 'Table 2'[Date], "mmmm" ) = _selectmonth ), MONTH ( 'Table 2'[Date] ) ) RETURN SWITCH ( TRUE (), HASONEVALUE ( 'Table 2'[Date].[Year] ) && HASONEVALUE ( 'Table 2'[Date].[Month] ) && HASONEVALUE ( 'Table 2'[Date].[Day] ), COUNTX ( FILTER ( ALL ( 'Table' ), 'Table'[Close date] > DATE ( _selectyear, _selectnumber, _selectday ) ), [Opportunity] ), HASONEVALUE ( 'Table 2'[Date].[Year] ) && HASONEVALUE ( 'Table 2'[Date].[Month] ), COUNTX ( FILTER ( ALL ( 'Table' ), 'Table'[Close date] > DATE ( _selectyear, _selectnumber, 1 ) ), [Opportunity] ), HASONEVALUE ( 'Table 2'[Date].[Year] ), COUNTX ( FILTER ( ALL ( 'Table' ), YEAR ( 'Table'[Close date] ) > _selectyear ), [Opportunity] ), 0 )

ratios = DIVIDE( [Count Process Date],[Count Close Date])

2. Result:

Best Regards,

Liu Yang

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