DAX Not working as expected when I join Date Dimension table with Fact Table

[ISAYYED]

Dear Experts,

Good day..!

I am new to Power BI DAX , this is my first query with power bi desktop, please bear with me for a lengthy description.  

I have below mention  two tables

1.  TRB_CUSTOMER  is customer table 

2.  Dim-Date is date dimension table

I have to extract count of common customer for selected years and  the logic for common customers is  

Customers must be present in the chosen year, the year before, and the year after..

Example : - if user selected 2017 I need the customer count where customers must be in 2016,2017 and 2018.

Example2 : - if user selected 2017 and 2021 I need the customer count where customers must be in 2016,2017 .2018, 2020,2021 and 2022.

DAX mentioned below is working as I expected when I do not make a cross join relationship between customer and date table.

but is not giving the right output when I join the customer and date table.

As per my analays I found that SUMMARIZE always giving 1 or total rowcount of table.

Could you please assist me to solve this issue.

Due to data confidential  I am unable toprovide PBIX file. 

MV - CoomonCustomer =
VAR SelectedYears = ALLSELECTED('Dim-Date'[Year])

VAR SeqTab =
DISTINCT(
SELECTCOLUMNS(
GENERATESERIES( 1, 3, 1 ),
"Seq", [Value]
)
)
VAR Allyears =DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(CROSSJOIN(SeqTab,SelectedYears),
"ALL_YEAR", SWITCH (
[Seq],
1, [Year] - 1,
2, [Year] ,
3, [Year]+ 1
)
),"ALL_YEAR" ,[ALL_YEAR])
)
VAR MetersByYear =
SUMMARIZE(
FILTER(
'TRB_CUSTOMER',
'TRB_CUSTOMER'[BYEAR] IN Allyears &&
'TRB_CUSTOMER'[CONSUMPTION] >0
),
'TRB_CUSTOMER'[CUSTOMER_ID],
"Years", COUNTROWS('TRB_CUSTOMER')
)
VAR MeterExistsAllYear =INTERSECT(
SELECTCOLUMNS(
FILTER(
MetersByYear,
[Years] >= COUNTROWS(Allyears)
),
"meter", [CUSTOMER_ID]
),
VALUES('TRB_CUSTOMER'[CUSTOMER_ID])
)

VAR CoomonCustomer =CALCULATE(DISTINCTCOUNT(TRB_CUSTOMER[CUSTOMER_ID]),
TRB_CUSTOMER[CUSTOMER_ID] in MeterExistsAllYear &&
'TRB_CUSTOMER'[CONSUMPTION] >0
)

RETURN CoomonCustomer

[Anonymous]

Something to consider

Never start with difficult things. Start simple. Then build on top of that.

In your example.

If you setup a relation based between customer and date then what happens is:

When you select a date (or date range) you filter customer

So if you select 2017 Power BI will filter your 2017 customers

Any dax you write will be against above. UNLESS you invalidate the filter in your dax (which you have not done)

Try building your thing without dax first

Select 2017 and count the number of customers in 2017 using no dax just count customer id or something

Then add 2016 to your selection etc

To me it just sounds silly to let the user select a year and the expect to do a +1 and -1

What are the requirements is i select 2014 and 2020 ? 2013 till 2021 or 2013-2015 and 2020-2022

Just let the user filter the data based on the years selected.

No dax needed and pretty straightforward.

Still going for dax ? First check if above works. Then try to build your dax.

[ISAYYED]

Dear @Anonymous 

Thank you for your quick support, i have invalidated context filter in  the dax and  it is working fine.