Forum Discussion
Calculate: Distinctcount Not Working Somehow
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.
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 PrevMOutput
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.
4 Replies
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 !Now PreviousMonth is resulting in completely blank values in Table.
peterpan did you change Table to your table name in that measure?
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 PrevMOutput
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.
