Issue with Date Table and Measures

[gdh9999]

I am working in POWER BI using Salesforce data and a calendar table in Power Query. This table has dates from 2018-2035 and has been working well for me until this one issue. I am trying to report the percentage of renewed customers, ie customers that bought last year and then again this year. I got this metric with a very simple division measure.

Denominator = last year's customers who have a last purchase date of this year

Numerator = all of last years customers

This measure pulls correctly when I do not have the purchase date affiliated with my date table, but once I activate the relationship with my date table, it shows 100% (ie I think what it is doing is removing anyone who's last gift date isn't this year, so its saying they all renewed).

I have removed all slicers, I do not have any additional filters on the visual card I am using, I have no filters on the page. What am I doing wrong? I am a novice so it might be something obvious. 

[v-prasare]

Thanks for participating in fabric community forum.

[v-prasare]

Hi @gdh9999,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @gdh9999,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @gdh9999,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Ashish_Mathur]

Hi,

Try these measures

Total = sum(Data[amount[)

Total in PY = calculate([Total],previousyear(calendar[date]))

N = calculate(distinctcount(Customer[Customer number]),filter(values(Customer[Customer number]),[total in PY]>0))

D = calculate(distinctcount(Customer[Customer number]),filter(calculatetable(values(Customer[Customer number]),previousyear(calendar[date]))),[total]>0))

R = divide([n],[d])

Hope this helps.

[Thaumaturgist]

Hello, can you please share your DAX code for the division measure and a screenshot of your model and the relationship between the information and Calendar table?

[gdh9999]

Screenshot of Measure I am using: 

Relationship to Calendar (it is inactive right now because activiating it is what triggers the unexpected result). Last gift date is the same as last purchase date.