Help with DAX 2

[AndyDD_UK]

In my Power BI report I have a page for 'sales by employee'.

Management want to select a period of weeks/month and see if any employees have not made any sales in a week (or more).

I have a matrix:

Column A: Tag as 'Pass/Fail'...essentially anyone with a missing week for the period selected is a fail.

Column B: Employee ID

Rows: Month/week 

Value = number of sales

An employee can only be a pass or fail. In other words if they have, for the period selected, 3 weeks of sales but one week without they are a fail.

Simplified example of table in Cube:

https://docs.google.com/spreadsheets/d/1TPqrXbS_Tbc3dY13q5PMb8QpNWYyzDIO9aN_oibt57Y/edit?usp=sharing

My date table has months and weeks

Thanks

[BeaBF]

@AndyDD_UK Hi! o create a Pass/Fail tag for employees who have missed a week of sales, you can create a measure that checks whether an employee has made a sale in all the selected weeks for the period:

Pass/Fail =
VAR SelectedWeeks = SELECTEDVALUES('Date'[Week])
VAR SalesWeeks = CALCULATETABLE(
VALUES('Date'[Week]),
FILTER('Sales', 'Sales'[Sales] > 0)
)
RETURN
IF(
COUNTROWS(SalesWeeks) = COUNTROWS(SelectedWeeks),
"Pass",
"Fail"
)

You can add this measure to your matrix as the first column (Column A) and the Employee ID column as the second column (Column B). For the rows, you can use the week or month column from your date table. Finally, for the values, use the Sales column from your Sales table.

The resulting matrix will show the Pass/Fail tag for each employee in each week/month for the selected period. If an employee missed a week of sales, they will have a Fail tag for that week/month, and if they made a sale in all selected weeks, they will have a Pass tag for the period.

BBF