filter 0 only when no prior results

[AFoulk]

Table = FILTER( ADDCOLUMNS( DISTINCT( CROSSJOIN( FILTER( VALUES('Stores[Store]), 'Stores'[Store] <> BLANK() ),
                                                                                           FILTER( VALUES('SKU'[SKU]), 'SKU'[SKU] <> BLANK() ),
                                                                                           FILTER(VALUES(Dates[WE]), Dates[WE] >= TODAY()-(365) &&

                                                                                                                                        Dates[WE] < TODAY() ) ) ),
                                                     "Sales", CALCULATE( SUM('Sales'[Qty]),

                                                                                       'Sales'[PO #] <> 0,

                                                                                       'Sales'[bolInvoiceSent] = TRUE() ) ),
                           [Sales] > 0 )

Here is what I currently have to build my Measure table that I want to use for calculating Average Monthly Sales based on a rolling 12 month sales history, but I would rather filter only where the Sales is 0 if there was no previous sales yet, but to actually include the 0 if in a week following a prior week that had sales.

Then I want to take the sum of those sales and divide by the DistinctCount of the total WeekEndings (WE). That total will then be multiplied by 52 to make a year and divided back down by 12 to determine a rough monthly average.

So the help I am looking for is first, how do I only filter the 0 sales if no prior weeks had sales but still retain where 0 sales occured after any previous weeks did contain sales, then second how to get the DistinctCount to work based on this table? The DistinctCount only works on a ColumnName but doesn't allow you to start typing any measure to try and tell it what to count without referencing a physical table's Column which defeats the purpose of why I want to do the DistinctCount within this created measure table.

[v-danhe-msft]

Hi @AFoulk,

From your description, I could not figure out your data structure, could you please post me more pictures about your data information or share your pbix file if possible?

Regards,

Daniel He

[AFoulk]

Hi Daniel,

What exactly do you mean by data structure?

[v-danhe-msft]

Hi @AFoulk,

Could you please post me the sample data in your different tables or share the pbix file if possible?

Regards,

Daniel He

[AFoulk]

I make a small sample explaining what I want a DAX measure to calculate for Average Sales.

How do I attach it onto this post?

[AFoulk]

FILTER( ADDCOLUMNS( DISTINCT( CROSSJOIN( FILTER( VALUES('Stores[Store]), 'Stores[Store] <> BLANK() ),
                                                                              FILTER( VALUES('SKU'[SKU]), 'SKU'[SKU] <> BLANK() ) ) ),
                                       "Mnthly Avg Sales", CALCULATE( SUM('Sales'[Qty]), 'Sales'[PO #] <> 0,
                                                                                                                          'Sales'[bolInvoiceSent] = TRUE(),
                                                                                                                          'Sales'[WE] >= TODAY() - 365,
                                                                                                                          'Sales'[WE] < TODAY() +1  )
                                       / DISTINCTCOUNT( 'Sales'[WE])
                                       * 52
                                       /12),
             [Mnthly Avg Sales] > 0 )

Now I tried this and figured I would just reference the actual table for my results measure as typed below, but then the results populating onto my table/matrix are all the same total (not breaking down by product per branch servicing the stores). I have the SUMX calculating in a parameter for % change in sales to manually increase/decrease these needs total.

SUMX('Store Avg Monthly Sales', 'Store Avg Monthly Sales'[Mnthly Avg Sales] * (1 + 'Needs'[Needs Change Value] ))