PowerBI: Total Count Distinct

Question

I need some help with my measure counting correct in total.

This is the MainTable:

With my Measure "CountPass" I count every ID distinct with the action "pass":

CountPass = CALCULATE(DISTINCTCOUNT(Workflow[ID]),Workflow[action]="pass")

Furthermore with my Measure CountPassPreweek I do same with reference on previous Week by using the a date table:

CountPassPreweek = 
var currentweek = SELECTEDVALUE(DateTable[WeekNum])
var currentweekday = SELECTEDVALUE(DateTable[WeekNo])
var currentyear = SELECTEDVALUE(DateTable[Year])
var maxweeknum = CALCULATE(MAX(DateTable[WeekNum]),all(DateTable))
Return
SUMX(
    if(currentweek = 1,
        DateTable[WeekNo] = currentweekday && DateTable[WeekNum] = maxweeknum && DateTable[Year] = currentyear - 1,
        DateTable[WeekNo] = currentweekday && DateTable[WeekNum] = currentweek -1 && DateTable[Year] = currentyear)),
[CountPass]
)

This is working so far but not showing the totals, so I have a second measure for doing that:

CountPreweekTotal = 
var _table = SUMMARIZE(DateTable,DateTable[Date],"_value",[CountPassPreweek])
return
SUMX(_table,[_value])

And here you see my problem: The measure doesn't count distinct like the "original" counting Measure as you see here

Hope somebody can help me with that.

Thanks a lot!

Answer 1

It's counting 3 since abc is getting counted twice the way your measure is written (since dates are separated in your SUMMARIZE).

Since you appear to have a proper date table, you should be able to use time intelligence functions to write this much more simply as

CountPassPreviousWeek =
CALCULATE ( [CountPass], DATEADD ( DateTable[Date], -7, DAY ) )

This should work for the total too.