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.
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?
Could you please post me the sample data in your different tables or share the pbix file if possible?
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])
[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] ))
Check out new user group experience and if you are a leader please create your group!
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.