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(
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!