Unable to get Count of stores for a branch which has sale between 0 to 200000 for last 3 month sales

[DikshaJaiswal]

Hi, 

I am unable to get Count of stores which satisfy condition on last 3 months sales.

I have below dataset Table1:

Country	Month (Real)	Day Date (Real)	Distributor ID	Branch ID	Distributor Branch	Store ID	Store Name	Sales
INDIA	2022M12	10-12-2022 00:00	9676358	2034234672	ABC	V00165	TMP	102727.2727
INDIA	2022M12	24-12-2022 00:00	9676358	2034234672	ABC	V00165	TMP	144090.9091
INDIA	2023M02	25-02-2023 00:00	9676358	2034234672	ABC	V00165	TMP	936.3637
INDIA	2023M02	11-02-2023 00:00	9676358	2034234672	ABC	V00165	TMP	2200.0002
INDIA	2022M11	12-11-2022 00:00	9676358	2034234672	ABC	V00165	TMP	11955.4547
INDIA	2022M01	10-01-2022 00:00	8873432	2009835247	ABC	V00165	QRS	94754.9874
INDIA	2022M01	24-01-2022 00:00	8873432	2009835247	ABC	V00165	QRS	1090.9091
INDIA	2023M02	25-02-2023 00:00	8873432	2009835247	ABC	V00165	QRS	936.3637
INDIA	2023M02	11-02-2023 00:00	8873432	2009835247	ABC	V00165	QRS	22700.0002
INDIA	2022M12	12-12-2022 00:00	8873432	2009835247	ABC	V00165	QRS	11545.4547

I have to calculate last 3 closed months sales. For e.g., if I select any date in february, it should return sales sum of Jan, Dec, Nov for that store. I created last 3 closed months sales measure which is working fine :

Last 3 Month Sales test = CALCULATE(SUM('Table1'[Sales]),DATESINPERIOD('Calendar Table'[Date],ENDOFMONTH(DATEADD('Calendar Table'[Date],-1,MONTH)),-3,MONTH)).

Here, Calendar Date is my Date Table which has one to many relationship single direction with 'Table1'.

After calculating last 3 months sales, I have to use below conditions to calculates count of stores for a branch:
Store Count: if(Last 3 Month Sales test>=0,if(Last 3 Month Sales test<=200000,DISTINCTCOUNT('Table1','Table1'[Store Name])))

Above measure(Store Count), is returning blank().

Can anyone help to calculate count of stores for a branch which has last 3 closed months store sales between 0 to 20000.

[v-stephen-msft · Accepted solution]

Hi @DikshaJaiswal ,

I modified the measures as follows.

Last 3 Month Sales test =
VAR _sel =
    SELECTEDVALUE ( 'Calendar Table'[Date] )
VAR _date1 =
    EOMONTH ( _sel, -1 )
VAR _date2 =
    EOMONTH ( _sel, -4 ) + 1
VAR _sum =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            [Store Name] = MAX ( 'Table1'[Store Name] )
                && [Day Date (Real)] <= _date1
                && [Day Date (Real)] >= _date2
        )
    )
RETURN
    _sum

Count =
CALCULATE (
    DISTINCTCOUNT ( Table1[Store Name] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        [Last 3 Month Sales test] <= 200000
            && [Last 3 Month Sales test] >= 0
    )
)

The dates of the slicer come from the [Calendar Date] table, which is not related to the primary table.

You can download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.