Count only store A and Bs opening per month

[Jay2022]

What i'm trying to work out is the numnber of store A and Bs (combined) opening for each month like so 

Nov 24 - 4

Dec 24 - 2

 and so on 

not counting opening dates for stores C and D 

I have come across something like this below but i'm not sure how to filter to include only store A and B or if i'm even on the right track at all. 

Count of Stores Opening=

CALCULATE(

    COUNTA('Table'[Store Opening Date]),

    FILTER(

        ALLSELECTED('Table’[Month Number]),

        ISONORAFTER('Table'[Month Number], MAX('Table'[Month Number]), DESC)

    )

)

Any help greatly apprciated 

[123abc · Accepted solution]

It looks like you’re on the right track with your DAX formula, but you’ll need to add a condition to filter for only stores A and B. Here’s how you can modify your formula to count the openings of only stores A and B:

Count of Stores Opening = 
CALCULATE(
    COUNTA('Table'[Store Opening Date]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Store Type] IN {"A", "B"} &&
        ISONORAFTER('Table'[Month Number], MAX('Table'[Month Number]), DESC)
    )
)

In this modified formula:

• The FILTER function now includes a condition to check if the Store Type is either “A” or “B”.
• This ensures that only the opening dates for stores A and B are counted.

You can adjust the Store Type field name if it differs in your dataset. This should give you the count of store openings for A and B combined, per month.

If you have any more questions or need further assistance, feel free to ask!

[Kedar_Pande · Accepted solution]

@Jay2022 

Count of Stores Opening = 
CALCULATE(
COUNTA('Table'[Store Opening Date]),
FILTER(
'Table',
'Table'[Store Type] IN {"A", "B"} &&
'Table'[Store Opening Date] <= MAX('Table'[Store Opening Date])
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Jay2022]

Fantastic thanks for your help, how would i build another measure creating a running total of the stores opening currently I have a column of dates (month/year) and (thanks to your help a column of stores openeing Jan 4, Feb 6 etc i would like anoyther column which shows a runnning total so Jan 4, feb 10 etc 

[123abc · Accepted solution]

It looks like you’re on the right track with your DAX formula, but you’ll need to add a condition to filter for only stores A and B. Here’s how you can modify your formula to count the openings of only stores A and B:

Count of Stores Opening = 
CALCULATE(
    COUNTA('Table'[Store Opening Date]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Store Type] IN {"A", "B"} &&
        ISONORAFTER('Table'[Month Number], MAX('Table'[Month Number]), DESC)
    )
)

In this modified formula:

• The FILTER function now includes a condition to check if the Store Type is either “A” or “B”.
• This ensures that only the opening dates for stores A and B are counted.

You can adjust the Store Type field name if it differs in your dataset. This should give you the count of store openings for A and B combined, per month.

If you have any more questions or need further assistance, feel free to ask!

[Jay2022]

Thank you

[123abc]

You are always wellcome.