Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Distinct count with previous date criteria

Hi, can anyone help me to understand what logic I need to apply to my existing table in order to return correct data?   Example table with underlying data called 'Table 1';   This is my cur...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, Anonymous ;

    I create a simple test file. and create a measure as follow:

    Measure = 
COUNTROWS(EXCEPT(VALUES('Table'[ID]),
SUMMARIZE(FILTER(ALL('Table'),
[Date]<=MIN('Table'[Date])&&[Date]>=EDATE(MIN('Table'[Date]),-12)),[ID])))

    the final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi, Anonymous ;

I create a simple test file. and create a measure as follow:

Measure = 
COUNTROWS(EXCEPT(VALUES('Table'[ID]),
SUMMARIZE(FILTER(ALL('Table'),
[Date]<=MIN('Table'[Date])&&[Date]>=EDATE(MIN('Table'[Date]),-12)),[ID])))

the final show:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi v-yalanwu-msft, thanks for this. Looks really good. I think I will be able to use this in my report.

 

Just one quick question about the measure you provided;

Does this measure return unique, distinct IDs? If not, what and where to add to the measure to return only distinct IDs?

 

Sorry, I am fairly new to Power BI DAX.

Thanks,