Excel Dax Count Unique Values and filter on current week automatically

Question

I am pretty new in wokrin with data models within Excel, especially in Dax formulas.

I have the following two tables in the data model.

Table_HS: Product Code - User

Table_IS: Product Code - Work Order - Date Created - Date Started

Now I want to get an Pivot table with the Product Code (Tabel_HS) and I want to know, how many Work Orders were created (Date Created) this week, and how many were started this week.

How would you manage that? Sure, I can add the Tabele_IS with =WEEKNUM the week number. But how can I filter the pivot automatically to the current week or the current and the last week? I don't want to filter every week the specific week number.

Best Regards Joshua

Olly Olly · Accepted Answer · 2019-05-31T12:54:38

Create two measures:

Work Orders Created This Week:=
VAR WeekCommencing = TODAY() - WEEKDAY(TODAY(),3)
VAR WeekEnding = WeekCommencing + 6
RETURN
    CALCULATE ( 
        DISTINCTCOUNT ( Table_IS[Work Order] ),
        Table_IS[Date Created] >= WeekCommencing && Table_IS[Date Created] <= WeekEnding
    )

and

Work Orders Started This Week:=
VAR WeekCommencing = TODAY() - WEEKDAY(TODAY(),3)
VAR WeekEnding = WeekCommencing + 6
RETURN
    CALCULATE ( 
        DISTINCTCOUNT ( Table_IS[Work Order] ),
        Table_IS[Date Started] >= WeekCommencing && Table_IS[Date Started] <= WeekEnding
    )

Excel Dax Count Unique Values and filter on current week automatically

1 Answers