Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

calculating new customers

Hello,

 

I have a FY filter on my dasboard, for example I am selected on FY24 currently, the FY starts in July so the data is populating for July 2023 - Dec 2023. Now I want to compare the IDs from the previous FY to current FY and return the count of IDs that are in the Current FY that were not in the previous. The comparsion needs to be July 2022 - Dec 2022 vs. July 2023 - Dec 2023 etc. because of the FY start date. This is the DAX i am using but the calculation is not populating correctly, what am I doing wrong?

 

example file 

 

in this file I created a measure called New Customers in Curreny FY, it should return 2 but its showing 7

 

New Customers in Current FY =

VAR CurrentFYYear = MAX('Calendar'[Fiscal Year Number])
VAR OneYearAgoMaxFYMonth = EDATE(MAX('Calendar'[Fiscal Month]), -12)
VAR OneYearAgoMinFYMonth = EDATE(MIN('Calendar'[Fiscal Month]), -12)

VAR NewCustomers =
    CALCULATETABLE(
        DISTINCT('Customers'[ID]),
        FILTER(
           ALL('Calendar'),
            'Calendar'[Fiscal Year Number] = CurrentFYYear
        )
    )

VAR PreviousCustomers =
    CALCULATETABLE(
        DISTINCT('Customers'[ID]),
        DATESBETWEEN('Customers'[Fiscal Month], OneYearAgoMinFYMonth, OneYearAgoMaxFYMonth)
             
        )

VAR NewCustomersCount =
    COUNTROWS(
        EXCEPT(
            NewCustomers,
            PreviousCustomers
        )
    )

RETURN NewCustomersCount
Need Help
Show and Tell
Tips and Tricks

7 Replies