Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KA95
Helper III
Helper III

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

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:

KA95_0-1656020866727.png

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:

KA95_1-1656021331089.png

 

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 🙂 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @KA95 ,

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])

 

vyangliumsft_0-1656472103007.png

 

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.

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @KA95 ,

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])

 

vyangliumsft_0-1656472103007.png

 

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
Super User
Super User

@KA95 , 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 )
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak 

 

KA95_0-1656058647969.png

 

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors