cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

The total during 12 months

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

 

YJAMOUS_2-1604089621602.png

 

 

 

1 ACCEPTED SOLUTION

@YJAMOUS 

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 

SU18_powerbi_badge

View solution in original post

9 REPLIES 9
Super User III
Super User III

@YJAMOUS 

 

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 

 

SU18_powerbi_badge

 

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

@YJAMOUS 

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 

 

SU18_powerbi_badge

 

So, I want to create a table that will look like this:

NamePoints in 12 months
Person A15
Person B30
Person C55
Person D30

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@YJAMOUS 

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 

SU18_powerbi_badge

View solution in original post

Super User III
Super User III

@YJAMOUS 

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 

SU18_powerbi_badge

Super User III
Super User III

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 

SU18_powerbi_badge

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?

NameReceived pointsDate (DD/MM/YYYY)
Person A101/10/2019
Person B205/11/2019
Person C301/5/2019
Person D401/4/2019
Person A51/9/2020
Person B101/7/2020
Person C251/4/2020
Person D301/5/2020

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors