Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I'm trying figure out how can I caculate the total of points within 12 months. If the total is above certain limit (e.g. 50), then I want to create an alert.
Example below:
Person A has total point 15 in 12 months ==> No Alert
Person B has total 30 points in 12 months. ==> No Alert
Person C has total 55 points in 12 months. ==> Alert
Person D has total of 70 points, but NOT in 12 months, but in 13 months. ==> No Alert
How can I achieve this?
Thanks in advance
Solved! Go to Solution.
@Anonymous
You can use this measure in the visual as described earlier:
Measure points =
VAR pointsLastDate_ =
SUMX (
ADDCOLUMNS (
ADDCOLUMNS(DISTINCT(Table1[Name]),"LastDate", CALCULATE(MAX(Table1[Date]))),
"Res",
VAR aux_ = [LastDate]
RETURN
CALCULATE (
SUM ( Table1[Received points] ),
Table1[Date] >= DATE ( YEAR ( aux_ ) - 1, MONTH ( aux_ ), DAY ( aux_ ) ),
Table1[Date] <= aux_,
ALLEXCEPT ( Table1, Table1[Name] )
)
),
[Res]
)
RETURN
pointsLastDate_
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
1. Place Table1[Name] in a table visual
2. Create this measure and place it in the visual
Measure =
VAR limit_ = 50 // Update as required
VAR check_ =
SUMX (
ADDCOLUMNS (
Table1,
"Res",
VAR aux_ = Table1[Date]
RETURN
1 * (
CALCULATE (
SUM ( Table1[Received points] ),
Table1[Date] >= DATE ( YEAR ( aux_ ) - 1, MONTH ( aux_ ), DAY ( aux_ ) ),
Table1[Date] <= aux_,
ALLEXCEPT ( Table1, Table1[Name] )
) > limit_
)
),
[Res]
) > 0
RETURN
IF ( check_, "ALERT", "No alert" )
3. See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB Thank you
I think I missed to say something
I don't want to show Alert. I'll handle the alert in traffic light. But I want to create a measure that just caculate the total for the 12 months.
@Anonymous
What's the total for 12 months? Given a name, there's a number of points over the last 12 months for each and every date. So you want the maximum of those? if not, what exactly?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
So, I want to create a table that will look like this:
Name | Points in 12 months |
Person A | 15 |
Person B | 30 |
Person C | 55 |
Person D | 30 |
Hi,
You may download my PBI file from here.
Hope this helps.
@Anonymous
You can use this measure in the visual as described earlier:
Measure points =
VAR pointsLastDate_ =
SUMX (
ADDCOLUMNS (
ADDCOLUMNS(DISTINCT(Table1[Name]),"LastDate", CALCULATE(MAX(Table1[Date]))),
"Res",
VAR aux_ = [LastDate]
RETURN
CALCULATE (
SUM ( Table1[Received points] ),
Table1[Date] >= DATE ( YEAR ( aux_ ) - 1, MONTH ( aux_ ), DAY ( aux_ ) ),
Table1[Date] <= aux_,
ALLEXCEPT ( Table1, Table1[Name] )
)
),
[Res]
)
RETURN
pointsLastDate_
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
or if you want the result in an actual table, create a new calculated table:
New table points =
ADDCOLUMNS (
ADDCOLUMNS (
DISTINCT ( Table1[Name] ),
"LastDate", CALCULATE ( MAX ( Table1[Date] ) )
),
"Points in 12 months",
VAR aux_ = [LastDate]
RETURN
CALCULATE (
SUM ( Table1[Received points] ),
Table1[Date]
>= DATE ( YEAR ( aux_ ) - 1, MONTH ( aux_ ), DAY ( aux_ ) ),
Table1[Date] <= aux_,
ALLEXCEPT ( Table1, Table1[Name] )
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Anonymous
Are you looking to check if the person was above the points limit in any 12 month period?
Can you sahare the sampel data not in a screen cap but in text-tabular format so that it can be copied?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB
Yes, I'm looking to check if the person was above the points limit in any 12 month period.
Is this format good?
Name | Received points | Date (DD/MM/YYYY) |
Person A | 10 | 1/10/2019 |
Person B | 20 | 5/11/2019 |
Person C | 30 | 1/5/2019 |
Person D | 40 | 1/4/2019 |
Person A | 5 | 1/9/2020 |
Person B | 10 | 1/7/2020 |
Person C | 25 | 1/4/2020 |
Person D | 30 | 1/5/2020 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
101 | |
69 | |
69 | |
43 |
User | Count |
---|---|
146 | |
106 | |
104 | |
89 | |
65 |