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.
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
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.
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.
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
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 @YJAMOUS
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 |
Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
User | Count |
---|---|
463 | |
183 | |
115 | |
61 | |
51 |
User | Count |
---|---|
437 | |
173 | |
125 | |
78 | |
74 |