The total during 12 months

[Anonymous]

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

[AlB]

@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 

[Anonymous]

@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.

[AlB]

@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 

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[AlB · Accepted 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 

[AlB]

@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 

[AlB]

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 

[Anonymous]

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