Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Store Expansion calculation

  Dears,  I have a customer table and these customers are opening like stores, I want to calculate the new stores that opened for each then calculates the average growth.    I have an open date a...
General Comment
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Would you like result below:

    count open = DISTINCTCOUNT('Table 2'[store id])

growth % =
VAR m1 =
    DISTINCTCOUNT ( 'Table 2'[store id] ) + 0
VAR m2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table 2'[store id] ) + 0,
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
VAR s = m1 - m2
RETURN
    IF ( m2 = 0, s / 1, s / m2 )

    Date table

    Date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", FORMAT ( [Date], "Mmm" ),
    "year-month", FORMAT ( [Date], "yyyy-mm" )
)

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi Anonymous 

Would you like result below:

count open = DISTINCTCOUNT('Table 2'[store id])

growth % =
VAR m1 =
    DISTINCTCOUNT ( 'Table 2'[store id] ) + 0
VAR m2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table 2'[store id] ) + 0,
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
VAR s = m1 - m2
RETURN
    IF ( m2 = 0, s / 1, s / m2 )

Date table

Date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", FORMAT ( [Date], "Mmm" ),
    "year-month", FORMAT ( [Date], "yyyy-mm" )
)

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.