more than one SELECTEDVALUE and DATESBETWEEN

[Mezmarianne]

I need help with filtering correctly in my second measure MTD LY Dynamic.

I have a table with Contacts. I need to count how many were created in a given month - and at the same time display how many were created in the same month the year before. 

The Contacts have a Created On Date and I have a related Date table with dates.

I can get this first measure to work:

MTD Dynamic =
VAR Month =
    SELECTEDVALUE ( 'Created On'[Month Number]; 1 )
VAR Year =
    SELECTEDVALUE ( 'Created On'[Year]; 2013 )
VAR FirstDayOfMonth =
    DATE ( Year; Month; 1 )
VAR LastDayOfMonth =
    DATE ( Year; Month + 1; 1 )
        - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FilteredContact[contactid] );
        DATESBETWEEN (
            FilteredContact[Created On Date];
            FirstDayOfMonth;
            LastDayOfMonth
        )
    )

However I can't get this one to work. 

When no year is selected and it defaults to First Month and 2016 - it shows the right number.

When other months are selected - it shows the right number for 2015 (I subtract 1 from 2016) and the selected month

Then when I select a year - it returns blank.

I think I have to throw an ALL() filter in there somewhere so the date filter does not filter out the rows I want to count.

MTD LY Dynamic =
VAR SelectedYear =
    SELECTEDVALUE ( 'Created On'[Year]; 2016 )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Created On'[Month Number]; 1 )
VAR FirstDayOfMonthLastYear =
    DATE ( SelectedYear - 1; SelectedMonth; 1 )
VAR LastDayOfMonthLastYear =
    DATE ( SelectedYear - 1; SelectedMonth + 1; 1 )
        - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FilteredContact[contactid] );
        DATESBETWEEN (
            FilteredContact[Created On Date]; 
            FirstDayOfMonthLastYear;
            LastDayOfMonthLastYear
        )
    )Month and Year selected. MTD worksMonth selected. No year. They both work, but MTD defaults to a month and year with no data.Another month selected. Both MTD and MTD LY works. MTD defaults to July 2013 and MTD defaults to July 2015.

[afzalphatan · Accepted solution]

Try this 

CALCULATE ( [MTD Dynamic], SAMEPERIODLASTYEAR(.....))

or try DATEADD() instead

[Mezmarianne]

SAMEPERIODLASTYEAR works. Not the way I intended, but it works. 

[v-xjiin-msft]

Hi @Mezmarianne,

Per my understanding about DAX date intelligence, we should not use Year - 1 to get last year. Because there exists leap year and normal year. So the Year - 1 may not return the correct last year. And I think your issue may exists here.

Thereby I would suggest you to use DATEADD() function to calculate the last year.

MTD LY Dynamic =
VAR Month =
    SELECTEDVALUE ( 'Created On'[Month Number], 1 )
VAR Year =
    SELECTEDVALUE ( 'Created On'[Year], 2013 )
VAR FirstDayOfMonth =
    DATE ( Year, Month, 1 )
VAR LastDayOfMonth =
    DATE ( Year, Month + 1, 1 )
        - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FilteredContact[contactid] ),
        DATESBETWEEN (
            FilteredContact[Created On Date],
            DATEADD ( FirstDayOfMonth, -1, YEAR ),
            DATEADD ( LastDayOfMonth, -1, YEAR )
        )
    )

Thanks,
Xi Jin.

[Mezmarianne]

Dateadd does not work as an input in DATESBETWEEN, but thanks for the suggestion.