topic Re: Count only store A and Bs opening per month in DAX Commands and Tips

Count only store A and Bs opening per month

Jay2022 — Mon, 25 Nov 2024 12:10:06 GMT

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

Re: Count only store A and Bs opening per month

123abc — Mon, 25 Nov 2024 12:32:48 GMT

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!

Re: Count only store A and Bs opening per month

Kedar_Pande — Mon, 25 Nov 2024 13:27:37 GMT

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

Re: Count only store A and Bs opening per month

Jay2022 — Mon, 25 Nov 2024 15:31:32 GMT

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

Re: Count only store A and Bs opening per month

Jay2022 — Wed, 27 Nov 2024 08:23:50 GMT

Thank you

Re: Count only store A and Bs opening per month

123abc — Wed, 27 Nov 2024 08:31:02 GMT

You are always wellcome.