Calculate: Distinctcount Not Working Somehow

[peterpan]

I have a Fact Table with repeating RowID and Entry Date which is linked with Calendar Table. Below is a glimpse of fact table:

One to Many relationship between Calendar, User and Project is already established. I am displaying a Table visual with User Name, Project Name and Unique Count of row_id, Unique Count of row_id for previous month by using calculate-

PreviousMonth = 
VAR UniqueRowID = DISTINCTCOUNT(FactTable[`row_id`])
VAR PrevM = CALCULATE(UniqueRowID, PREVIOUSMONTH(FactTable[EntryDate]))
RETURN
PrevM

I am getting the count of Unique Row ID and Previous Month as the same. I don't know where I am going wrong. I also tried with Calculate(UniqueRowID, DATEADD(FactTable[EntryDate],-1,MONTH)) but there's no change in result. Please assist.

[Anonymous · Accepted solution]

Hi @peterpan 

Based on your description, you need to use the date of calendar table in previoudmonth() function. you can try the following measure.

PreviousMonth =
VAR PrevM =
    CALCULATE (
        DISTINCTCOUNT ( FactTable[row_id] ),
        PREVIOUSMONTH ( 'Calendar'[Date] )
    )
RETURN
    PrevM

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ahadkarimi]

Hi @peterpan, give this a try, and if you encounter any issues, let me know.

PreviousMonth = 
CALCULATE(
    DISTINCTCOUNT(FactTable[row_id]),
    PREVIOUSMONTH('Table'[Date])
)

Did I answer your question? If so, please mark my post as the solution! Your Kudos are much appreciated! Proud to be a Resolver II !

[peterpan]

Now PreviousMonth is resulting in completely blank values in Table.

[ahadkarimi]

@peterpan did you change Table to your table name in that measure?