Average values of a column within the week before with conditions in the month before

[liselotte]

I have a table 'TableSales' with 3 columns 'Date' (in date format), 'Cashier', 'Sale Amount', and another table 'TableDate' with 2 columns 'SelectedDate' (in date format) and 'SelectedDate_text' (date but in text format so that I can have the last date as "The latest day" instead of the date).

I use 'TableDate'[SelectedDate_Text] to create a slicer so that users can choose a desired date.

I would like to create a measure to calculate the average Sale Amount of Cashiers per day during the latest 7 days until the selected date in the slicer. I can do that with the following query:

average = CALCULATE(

      SUM('TableSales'[Sale Amount])/COUNT('TableSales'[Cashiers])

      ,FILTER('TableSales',

          'TableSales'[Date <= MAX('TableDate'[SelectedDate])

          && 'TableSales'[Date >= MAX('TableDate'[SelectedDate])-6))

However, now I want to filter out all Cashiers with Sale Amount = 0 or blank during the latest 1 month until the selected date because those cashiers are assumed to be broken and shouldn't be taken into the calculation of average. How should I include that into my measure? Thank you.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Dangar332]

Hi, @liselotte 

not sure but try to below measure

average = 
var a = selectedvalue('TableDate'[SelectedDate])
var a1 = edate(selectedvalue('TableDate'[SelectedDate]),-1)
return
CALCULATE(
      SUM('TableSales'[Sale Amount])/COUNT('TableSales'[Cashiers])
      ,FILTER(
          filter(
              'TableSales',
              'TableSales'[Date] >= a1 &&
              'TableSales'[Date]=< a && 
              'TableSales'[Sale Amount])<> blank()
          )
          'TableSales'[Date] <= MAX('TableDate'[SelectedDate]) &&
          'TableSales'[Date >= MAX('TableDate'[SelectedDate])-6
       )
)

[liselotte]

Thank you for your reply, but your provided query will remove all rows with blank Sale Amounts durinh the latest month while what I want is to only remove rows with Cashiers having all Sale Amounts = 0 or blank during the latest month. For Cashiers having sometimes Sale Amount <> 0 during the latest month, even the rows of them with Sale Amount = 0 should be kept in the data to calculate the average.