CODEWITHSUNDEEP
powerbidaxdata-analysisbusiness-intelligence
Guissous Allaeddine
Guissous Allaeddine

Reputation: 445

How to apply Date filter to SamePeriodLastYear in Power BI DAX?

I want to calculate the Active Customer number change between two years, so I created a DAX measure to calculate the customer count for this year and other for last year:

Active Customer : I want to explain what that means:

There are two columns in the customer table : FirstRegistrationDate and RegistrationExpiryDate

the active customer for a specific date must have a date in FirstRegistrationDate before the select date.

and RegistrationExpiryDate is biger than the select date.

Example :

For date: 01/31/2021

FirstRegistrationDate < 01/31/2021.

RegistrationExpiryDate > 01/31/2021.

The Measuers:

CustomerCount :

DISTINCTCOUNT(VWCRs[id])

CustomerCountLastYear :

CALCULATE(DISTINCTCOUNT(VWCRs[id]), SAMEPERIODLASTYEAR(VWCRs[FirstRegistrationDate].[Date]),         
SAMEPERIODLASTYEAR(VWCRs[RegistrationExpiryDate].[Date]))

Then

Customer YOY = CustomerCount - CustomerCountLastYear

Customer YOY% = (CustomerCount - CustomerCountLastYear) / CustomerCountLastYear

without applying date filters: the results of CustomerCount and CustomerCountLastYear are different and seem logical.

The Problem is when I use a two filter to specify the FirstRegistrationDate and other for RegistrationExpiryDate I got the same result for both CustomerCount and CustomerCountLastYear and Customer YOY% = 0.0%.

I use the Auto-Date of power bi.

enter image description here

Upvotes: 0

Views: 2061

Answers (1)

Mik
Mik

Reputation: 2103

the problem is in Auto-Date. Your slicers comes from table date fields and Auto-Date, in the measure - VWCRs[...].[Date], simply ignores your slicers (no idea why). In fact, your data is limited only by one year from the latest date in the table and this is the reason why you get the value Customer YOY% = 0.0%. So, to make it works you can change the measure as below or make 2 calendars as I advice you in comments. If you will use calendars slicers should made of them and use calendars in SamePeriodLastYear expressions. Both variants are checked and works.

VAR RegDates = 
    DATESBETWEEN(
        VWCRs[FirstRegistrationDate]
        ,MIN(VWCRs[FirstRegistrationDate])
        ,MAX(VWCRs[FirstRegistrationDate])
    )
VAR ExpDates=
    DATESBETWEEN(
        VWCRs[RegistrationExpiryDate]
        ,MIN(VWCRs[RegistrationExpiryDate])
        ,MAX(VWCRs[RegistrationExpiryDate])
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT(VWCRs[id])
        ,SAMEPERIODLASTYEAR(RegDates)
        ,SAMEPERIODLASTYEAR(ExpDates)
    )

Upvotes: 1

Related Questions