Need to show total sale value of ID in first month it appeared even it is present in other months

[AshDil]

Hi,
I have data as follows:

DATE	ID	Value
1/1/2021	1011	100
1/1/2021	1012	100
2/1/2021	1011	200
3/1/2021	1011	250
8/1/2021	1020	400
9/1/2021	1011	300
12/1/2021	1011	220

Eg: ID = 1011, it is present in Jan, Feb,Mar,Sep,Dec.Need to show total sale value for ID in the first month it appeared in trend chart. Need to show total sale value of 1011 ID = 100+200+250+300+220 = 1070 in Jan Month (since it appeared first in Jan)

Please help me to do.

Thanks,

AshDil

[parry2k · Accepted solution]

@AshDil change my measure to this and it will work

Measure = 
VAR __firstDate = CALCULATE ( MIN ('Table'[DATE] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
VAR __sum = CALCULATE ( SUM ('Table'[Value] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
RETURN 
IF ( MAX ('Table'[DATE] ) == __firstDate, __sum ) + 0

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@AshDil change my measure to this and it will work

Measure = 
VAR __firstDate = CALCULATE ( MIN ('Table'[DATE] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
VAR __sum = CALCULATE ( SUM ('Table'[Value] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
RETURN 
IF ( MAX ('Table'[DATE] ) == __firstDate, __sum ) + 0

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@AshDil how you are planning to visualize it? You should have explained it in your original post. The question you asked was solved but now you are asking something totally different.

[AshDil]

Hi @parry2k  & @Ashish_Mathur ,

I need to show output as follows in trend chart

Please help me to do it.

Thanks,

AshDil.

[Ashish_Mathur]

Hi,

How is this going to help.  I have requested you in my previous message to show the exact result in a column

[AshDil]

Hi @Ashish_Mathur ,

When I use ID column in the visual I need the Output as follows:

DATE	ID	Value	Result
1/1/2021	1011	100	1070
1/1/2021	1012	100	100
2/1/2021	1011	200
3/1/2021	1011	250
8/1/2021	1020	400	400
9/1/2021	1011	300
12/1/2021	1011	220

If I eliminate ID column in the visual the output I need is

Date	Result
1/1/2021	1170
2/1/2021
3/1/2021
8/1/2021	400
9/1/2021
12/1/2021

The purpose of this requirement is the ID will be created in one month with some amount (eg: ID = 1011 and month created = Jan and amount  = 100) going forward there will be some negotiation or addition of value to the original amount with time (if we moved from Jan to Feb there is addition of 200 to the ID 1011), so the user wanted to see entire sum for the ID in first month it created. As ID column will have many ID numbers, they want see month wise which is high level.

Please help me with this.

Thanks,

AshDil.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AshDil]

Hi @parry2k ,

Sorry for not giving that clarity in my post. I need to show the calculation in trend chart with out using ID field in the visual, Please help me with a way to show it in trend chart.

Thanks,

AshDil.

[parry2k]

@AshDil here is the measure which will get you going:

Measure = 
VAR __firstDate = CALCULATE ( MIN ('Table'[DATE] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
VAR __sum = CALCULATE ( SUM ('Table'[Value] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
RETURN 
IF ( MAX ('Table'[DATE] ) == __firstDate, __sum )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AshDil]

Hi @parry2k ,

This measure has helped me to get what I was expecting but I'm trying to show this measure in Trend chart where I should not use ID field, in that case I'm getting blank visual. Please help me to do it.

Thanks,

AshDil.

[Ashish_Mathur]

Hi,

Not sure of why you want this, but enter this calculated column formula

=if(CALCULATE(countrows(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[DATE]<=EARLIER(Data[DATE])))=1,CALCULATE(SUM(Data[Value]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))),BLANK())

Hope this helps.

[AshDil]

Hi @Ashish_Mathur ,

I have tried to create a calculated column as you mentioned but some of the ID are missing with that calculation. Please help me to get all ID.

Thanks,

AshDil.

[Ashish_Mathur]

Hi,

I am not sure of what you want but try this calculated column formula

=CALCULATE(SUM(Data[Value]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))

[AshDil]

Hi @Ashish_Mathur ,

I tried using this calculation,I'm getting all ID's this time but the values getting rolled up to high values. Please help me to do it.

Thanks,

AshDil

[Ashish_Mathur]

Hi,

Show the exact expected result in a spare column.

[parry2k]

@AshDil is this what you are looking for?

[AshDil]

Hi @parry2k,

Yes I want the output in same way.

Thanks,

AshDil. 

[lbendlin]

Use an ALLEXCEPT() filter on the ID when you calculate the SUM(Value).

[AshDil]

Hi @lbendlin ,

I have tried using ALLEXCEPT(), getting total sum value of ID in each month that is present. Getting output as follows:

Thanks,

AshDil.