Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dax DisctinctCount with date condition

So here is my problem, i need to calculate the quantity of different "DOC Number" but only if the Register date is < than Sales Date, in this case the "DOC NUMBER" repeats because it might be 2 diffe...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi v-xicai jdbuchanan71 

     

    i've tryed both the solutions you guys gave, sadly idk why it didnt work so what i did to make it work is to create a columm in the Sales Tables called "Register Date" with this fucntion 

     

    RegisterDate= Related('MembershipTable'[Registerdate]

     

    with this and the a mix of the measure i used at the beginning and parts of yours it worked 

     

    Tickets = CALCULATE(DISTINCTCOUNT('SalesTable'[DOC Number]),FILTER('SalesTable','MembershipTable'[RegisterDate]<='SalesTable'[SalesDate]))
     
    Thanks a lot for your solutions 3 minds think better than 1 :) 
jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

Hello Anonymous 

Is it because you used >= instead of just > in your measure?  When I test it the logic works if I use < and swap the order.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, thanks for the answer! but my problem persist i forgot to mention that "Register Date" is in another table i have like a sales table and a membership table

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Anonymous 

    Can you share a sample .pbix with the tables and relationships?  It helps a lot to know how the data is set up.