Get value from a field based on another field

[ToNo87]

I have a table something like this

Value | Datetime (date)

2          12/12/2016

3          12/12/2016

2          12/12/2016

2          12/12/2016

4          12/12/2016

2          12/14/2016

What I want to do is to create a measure where I get a distinct count of the "value" datafield, based on the "Date" column where the date column is NOW - 1 month.

How do I acheive this? 

[PavelR · Accepted solution]

Hi @ToNo87

create measure like

Distinctcount M-1 = CALCULATE(DISTINCTCOUNT(Table1[Value]);DATEADD(Table1[Date];-1;MONTH))

Regrds.

Pavel

[ToNo87]

I'm getting a warning that says "The syntax for ';' is incorrect. " (I am using Power BI Desktop). 🙂 

[PavelR]

It works fine. Maybe that when you have edited the formula you have also removed some bracket or something.

Regards.

Pavel

[ToNo87]

Thanks @PavelR

This is the error message I got:

The syntax for ';' is incorrect. (DAX(CALCULATE(DISTINCTCOUNT(Data[Value]);DATEADD(Data[Time];-1;MONTH)))).

this is the measure I added:

Distinctcount -1 = CALCULATE(DISTINCTCOUNT(Data[Value]);DATEADD(Data[Time];-1;MONTH))

Note that Data[Value] is hash id (text) and Data[Time] is date/time if that matters.

[Vvelarde]

@ToNo87

Try replacing ; by ,

[ToNo87]

It worked, thanks!