Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter by datediff in Analysis Services

Hello,  I have created a calculation  Measure = CALCULATE( DISTINCTCOUNT([Supplier Name]), DATEDIFF(max([Creation Date]), ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , Try like

    Measure = CALCULATE(
    COUNTX(values(Table[Supplier Name]),table[Supplier Name]),
    filter(table, DATEDIFF(max([Creation Date]),
    TODAY(),year)=1
    ))

    OR

    Measure = countx(summarize(Table , Table[Supplier Name], "_1",
    calculate(lDISTINCTCOUNT([Supplier Name]),
    filter(table, DATEDIFF(max([Creation Date]),
    TODAY(),year)=1
    ))),[_1])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , try like

Measure = CALCULATE(
DISTINCTCOUNT([Supplier Name]),
filter(table, DATEDIFF(max([Creation Date]),
TODAY(),year)=1
))

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Seems like a plausible solution amitchandak but this is what I got when I tried to use it

SO it works only when I have the field of suppliers in the view, not in the card, isn't it strange?

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try like

    Measure = CALCULATE(
    COUNTX(values(Table[Supplier Name]),table[Supplier Name]),
    filter(table, DATEDIFF(max([Creation Date]),
    TODAY(),year)=1
    ))

    OR

    Measure = countx(summarize(Table , Table[Supplier Name], "_1",
    calculate(lDISTINCTCOUNT([Supplier Name]),
    filter(table, DATEDIFF(max([Creation Date]),
    TODAY(),year)=1
    ))),[_1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks! I was thinking of an addition of an "or" operator something like

      Measure = countx(summarize(Table , Table[Supplier Name], "_1",
      calculate(lDISTINCTCOUNT([Supplier Name]),or(
      filter(table, DATEDIFF(max([Creation Date]),
      TODAY(),year)=1),max([Creation Date]=Blank())
      ))),[_1])

      but it doesn't seem that or works in analysis services that way, so I have solved this issue with creating and adding the different measures. Any better idea on this amitchandak ?
      Thanks again!