Need Help - I want to get list of customers based on continuous state of monthly moving average %

[dmalviya]

Hi All,

Data Model - 

1. Fact Sales (Monthly )
2. Item (product) dimension
3. Date dimension 
4. Customer Dimension

I have already completed below calculation (measures) -

• 12 Month Sales Moving Average : Sales calculated based on 12 month moving average
• 12 Month Sales Moving Average pct: % of moving 12 Month Sales Moving Average
• 12 Month Sales Average Indicator: values are Increasing, Decreasing or No change based on 12 Month Sales Moving Average pct

Requirement -

i want to show list of customers whose sales is continuously following specifid Indication like Increasing, Decreasing or No change. So it is based on 12 Month Sales Average Indicator calculated measure. User will define last N month value in slicer.

I have already created Power BI file with above measure & all slicers. Now i want to complete Measure -

Last N Month Sales Moving Growth --> For example if user select 3 month and Indicator value from slicer = Increasing, it should show list of cutomers which have  12 Month Sales Average Indicator value continuously Increasing from last 3 months and so on. This measure should show 1 if condition is true or BLANK().

Please help me to complete above measure.

Please find Power BI file here -

https://1drv.ms/u/s!AgZ1uNPRA6n_g3fW76Jp7PFaMjid

Thanks

Deepak

[v-chuncz-msft · Accepted solution]

@dmalviya,

You may check the measure below.

Measure =
IF (
    COUNTROWS (
        FILTER (
            SUMMARIZE ( DimDate, DimDate[Date].[Year], DimDate[Date].[Month] ),
            DATEDIFF ( CALCULATE ( MAX ( DimDate[Date] ) ), MAX ( DimDate[Date] ), MONTH )
                < [LastNMonthSlicerValue]
                && [12 Month Sales Average Indicator]
                    = SELECTEDVALUE ( GrowthIndicator[IndicatorValue] )
        )
    )
        = [LastNMonthSlicerValue],
    1
)

[dmalviya]

Hi Sam, Thanks for providing measure. It is partially woking. It is not filtering customers. If i use the measure on simple table it should only display only those list of customers who has satisfied the criteria.

So for example out of 5 customers, there is only one customer who is continuously in "Decreasing" state from last month 3 month for selected Item. It should display only that customer. Sorrry if i missed to explain earlier. 

Can it be possible?

Thanks

Deepak

[dmalviya]

Hi , Thanks for providing measure.It is partially working. it should consider customer based on last selcted month & items.

If i use the measure to show on simple table it should only display only those list of customers who has satisfied the criteria.

So for example out of 5 customers, there is only one customer who is continuously in "Decreasing" state from last month 3 month for selected Item. It should display only that customer. Measure should return 1 if above condition is satified else BLANK(). I can filter the visual to hide BLANK() values.

Please suggest .

Thanks Deepak

[v-chuncz-msft]

@dmalviya,

It seems to work.