DAX to calculate distinct count based on specific filters

[breezee]

Hi

I am trying to create a DAX formula that will provide me with a value for 'This Week' and 'Last Week' that is less a specified number...in this example 35. However, the value for 'Last Year' must remain untouched.

The formula that I am currently using is below. I am not able to use the calculate function as a true/false function so I have tried different ways but cannot achieve the result required.

CALCULATE (

    DISTINCTCOUNT ('Table1’[ID] )

        - 'Directs'[calc_CT],

    'Table 2’[Type] = "New",

    'Date'[calc_Weekly Name] = "Last Week"

        || 'Date'[calc_Weekly Name] = "This Week"

)

As expected the values for This Week and Last Week calculate correctly but the value for Last Year does not as I guessing it is viewing the base as 0.

I know that I am overlooking something fundamentally simple, however, any assistance or guidance in the right direction would be greatly appreciated.

Many thanks, breezee

[v-huizhn-msft]

Hi @breezee,

If there is relationship among Table1,Table2, Directs and Dates table? Could you please create dummy sample data, so that we can post solution which is close to what you want.

Best Regards,
Angelia