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, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors