cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mezmarianne Regular Visitor
Regular Visitor

more than one SELECTEDVALUE and DATESBETWEEN

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 + 11 )
        - 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 + 11 )
        - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FilteredContact[contactid] );
        DATESBETWEEN (
            FilteredContact[Created On Date]; 
            FirstDayOfMonthLastYear;
            LastDayOfMonthLastYear
        )
    )ScreenHunter 885.pngMonth and Year selected. MTD worksScreenHunter 886.pngMonth selected. No year. They both work, but MTD defaults to a month and year with no data.ScreenHunter 887.pngAnother month selected. Both MTD and MTD LY works. MTD defaults to July 2013 and MTD defaults to July 2015.

1 ACCEPTED SOLUTION

Accepted Solutions
afzalphatan Member
Member

Re: more than one SELECTEDVALUE and DATESBETWEEN

Try this 

 

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

or try DATEADD() instead

4 REPLIES 4
v-xjiin-msft Super Contributor
Super Contributor

Re: more than one SELECTEDVALUE and DATESBETWEEN

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.

afzalphatan Member
Member

Re: more than one SELECTEDVALUE and DATESBETWEEN

Try this 

 

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

or try DATEADD() instead

Mezmarianne Regular Visitor
Regular Visitor

Re: more than one SELECTEDVALUE and DATESBETWEEN

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

Highlighted
Mezmarianne Regular Visitor
Regular Visitor

Re: more than one SELECTEDVALUE and DATESBETWEEN

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