Count of Stores with at least one sale over 3 months

[dswinden]

I have a measure that calculates the sales of a product over a rolling 3 months period (user will select the month from a slicer).  So for example below in screen shot, there were only 5 stores that ordered in December as represented in DEC Count column.  However the DEC R3M Sales column is the total sales over Oct/Nov/Dec for that store.  Then the count is "If sales > 0 = 1 , 0".  THere were 8 stores that ordered product at least once over this three month period.

My R3M Sales measure in PBI is working as expected and is below;

R3M | WS Sales ($$) =

CALCULATE([MTD | WS Sales ($$)],DATESINPERIOD('Date XREF'[Month Start],LASTDATE('Date XREF'[Month Start]),-3,MONTH))

However my listings measure is not as it is showing me the 5 stores ordered in December;

R3M | Listings =

CALCULATE(DISTINCTCOUNTNOBLANK(store name),FILTER(DISTINCT(store name),CALCULATE([R3M | WS Sales ($$)])))

There are two tables in this model.  SALES, and DATE.  The relationship is many to one FROM Sales table (order date) TO Date table (Day) with a single direction cross filter.

Ideas to resolve?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[speedramps]

@lbendlin 
Thanks, I think I have answered the question just a few seconds before your post

[speedramps]

Download my example PBIX solution from Onedrive

Click here  

Create another identical calendar table, call it Picklist and  set the retalationships to inactive

Create measures 

Has data = 
CALCULATE(
INT(NOT(ISEMPTY(Yourdata))),
USERELATIONSHIP(Picklist[Date],Yourdata[Date]))

Sales in 3 months = 
var mypick = SELECTEDVALUE('Picklist'[Month offset])
RETURN
CALCULATE(SUM(Yourdata[Sales]),
'Calendar'[Month offset] >= mypick - 2 &&
'Calendar'[Month offset] <= mypick )

Use the picklist period (not the calendar) in your slicer with the measure to filter your slicer

Use the calendar period (not the picklist) in your visual

Test and then adapt to satisfy your exact requirements

Please click the [accept as solution] and the thumbs up button. Thank you

[dswinden]

Thank you, but this is not the solution.  In the data you created you had Store # 3 having sales of $4705 in November, $2835 in December and 0 in January for a total sales amount of $7,540 over past 3 months

The measure i wrote in my initial question of;

R3M | WS Sales ($$) =

CALCULATE([MTD | WS Sales ($$)],DATESINPERIOD('Date XREF'[Month Start],LASTDATE('Date XREF'[Month Start]),-3,MONTH))

Totals the sales over the 3 month period which using your data would be $4705+2835+0 = $7540 total

but that was not the question I asked as my measure works fine for total sales over an R3M period

the issue is a distinct count of stores with sales greater than $0.  In this case Store 3 ordered more than $0 during this time period, so the count should be 1 for Jan 2025 R3M but as you can see in below, I added this distinct count measure using your "Sales in 3 Months" measure, and resulted in the same output as my error that I highlight in my initial question.