DAX - Filter between two specified dates

0
votes

  1. Table 1 (Combined Hours) I have a list of [payweek], which is many repeated Image of Combined Table

  2. Table 2 (All Scripts), I have a list of jobs and two dates, [Scripting Date] and [R4PreScriptDate] Image if All Scripts table

  3. I am trying to find the number of unique [payweek]s between the [Scripting Date] and the [R4PreScriptDate]

  4. The DAX formula is below. My result is always 3. However, I am expecting variable answers (0,1, 2,3,4 or 5)

    R4PreScriptWkCt = CALCULATE(DISTINCTCOUNTNOBLANK(CombinedHours[payWeek]),FILTER(CombinedHours, 
CombinedHours[payWeek] <= MAX (AllScripts[Scripting Date] )
&& CombinedHours[payWeek] >= MAX (AllScripts[R4PreScriptDate] ) ))

Thank you,

1
filterpowerbidax
If you can provide some example data and expected result it would be much more easy to help you. – Agustin Palacios
thank you for the feedback.I added the two tables. – Rachel Kelly

1 Answers

0
votes

I assumed that both tables are related by Job Number. I created the following measure:

R4PreScriptWkCt =
VAR __date2 = MAX ( AllScripts[R4PreScriptDate] )
VAR __date1 = MAX ( AllScripts[Scripting Date] )
VAR __job = MAX ( AllScripts[Job Number] )
VAR __subTable =
    FILTER (
        CombinedHours,
        CombinedHours[Job Number] = __job
            && CombinedHours[Week] >= __date1
            && CombinedHours[Week] <= __date2
    )
RETURN
    CALCULATE ( DISTINCTCOUNTNOBLANK ( CombinedHours[Week] ), __subTable )

Hope it helps you.