topic Re: Average values of a column within the week before with conditions in the month before in DAX Commands and Tips

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

liselotte — Sun, 11 Feb 2024 16:50:36 GMT

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.

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

Dangar332 — Sun, 11 Feb 2024 17:46:20 GMT

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 ) )

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

lbendlin — Sun, 11 Feb 2024 19:43:01 GMT

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-Forum/ba-p/963216

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/1447523

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

liselotte — Mon, 12 Feb 2024 12:43:24 GMT

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.