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

[Meep]

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

[Anonymous · Accepted solution]

Hi  @Meep ,

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

[amitchandak]

@Meep , My HR blog can help

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

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) ))