Date Measures are not adding up to total value, not sure why

[Anonymous]

Hi there, 

I have two measures that count ID's from two different timescales one that counts the last 30 days from now, and another that counts 30-90 days. However, the 30-90 days isn't adding up correctly, and I'm not sure why, as shown below:

I have the date filtered from the last 90 days. If you add up the last 30 days, and last 30 to 90 days, it should be the exact same value as the User Count, yet it's much bigger and I don't understand why. Here's the code for the three measures:

User Count = DISTINCTCOUNT('Candidate Activity'[userid])

Last 30 days =
CALCULATE (
(DISTINCTCOUNT('Candidate Activity'[userid]),
DATESINPERIOD ( 'Candidate Activity'[active_date], MAX ( 'Candidate Activity'[active_date] ), -30, DAY )
)

Last 30 to 90 Days =
var lstdate = LASTDATE('Candidate Activity'[active_date])
var lstdateless90 = lstdate -90
var lstdateless30 = lstdate -30
return
CALCULATE([User Count], DATESBETWEEN(Dates[Date], lstdateless90, lstdateless30))
 

So the date that is being filtered is a column called [active_date] which is used in a date table as shown below:

Here is the code for that date table below:

Dates = ADDCOLUMNS ( CALENDAR (FIRSTDATE('Candidate Activity'[active_date]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

I'm unsure why it's not being calculated correctly, is there something I'm doing wrong here? Because I'm completely at a loss. Any help would be greatly appreciated 🙂 

[Anonymous · Accepted solution]

Hi  @Anonymous ,

This may be related to your data.

If your table has dates 2022.1.1 – 2022.4.1.

[User Count] is to count all the data without duplicates in the entire table, such as A, B, C, D, E.

[Last 30 days] is to count the data without duplicates in the table from 2022.3.2 - 2022.4.1, such as A, B, C.

[Last 30 - 90days] is to count the data without duplicates in the table from 2022.1.1 - 2022.3.2, such as A, D, E.

If all three measures have an A when they are counted, they will be counted as the individual of the three measures, not equivalent to a whole with only one A.

So [Last 30 days]+ [Last 30 - 90days] =6 >[User Count]=5.

If you change the function to Count, they add up to the same.

Last 30 to 90 Days =
var _lastdate = LASTDATE('Candidate Activity'[actrive_date])
return
CALCULATE(COUNT('Candidate Activity'[userid]),FILTER(ALL('Candidate Activity'),
'Candidate Activity'[actrive_date]>=
DATE(YEAR(_lastdate),MONTH(_lastdate),DAY(_lastdate)-90)&&'Candidate Activity'[actrive_date]<DATE(YEAR(_lastdate),MONTH(_lastdate),DAY(_lastdate)-30)
))

Last 30 Days =
var _lastdate = LASTDATE('Candidate Activity'[actrive_date])
return
CALCULATE(COUNT('Candidate Activity'[userid]),FILTER(ALL('Candidate Activity'),
'Candidate Activity'[actrive_date]>=
DATE(YEAR(_lastdate),MONTH(_lastdate),DAY(_lastdate)-30)&&'Candidate Activity'[actrive_date]<=_lastdate)
)

User Count = COUNT('Candidate Activity'[userid])

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous , Not very clear. Try measure in one of the following

Last 30 to 90 days =
CALCULATE (
(DISTINCTCOUNT('Candidate Activity'[userid]),
DATESINPERIOD ( 'Candidate Activity'[active_date], MAX ( 'Candidate Activity'[active_date] )-30, -60, DAY )
)

or

Last 31 to 90 days =
CALCULATE (
(DISTINCTCOUNT('Candidate Activity'[userid]),
DATESINPERIOD ( 'Candidate Activity'[active_date], MAX ( 'Candidate Activity'[active_date] )-31, -60, DAY )
)

[Anonymous]

Hi @amitchandak 

Thanks for your help but both are getting errors, it says unexpected tuple in both of them?