Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MaskedNico
Regular Visitor

Get the sum at the previous date with non continuous dates

Hello,

 

I am totally new in Power BI.

 

I have a table similar as the one displayed at the end of this post (we'll call it "Table").

 

I am trying to calculate the growth of the sum of all Items for all IDs from one date to another, knowing that :

  • Date are not continuous
  • Items of each IDs change at the same date

 

I have the followings measures / calculated columns already in place and working, that could help  :

  • nItems - Measure : Sum of all items (to be used in appropriate context for calculating the sum at a specific date)
    nItems = SUM(Table[Items])
  • previousDate - Calculated column : Previous existing date before the current (if we are working with 22/10/2022, then previousDate is 17/10/2022)
    previousDate = CALCULATE(MAX(Table[Date]), FILTER(Table, Table[Date] < EARLIER(Table[Date]))

 

I am now trying to create a measure that sum the Items of the date previous to the current one, but all the solutions I applied were not working. Based on https://community.powerbi.com/t5/Desktop/Previous-date-values/m-p/134347 - I replaced the second calculated column in the post by a measure - I created the following measure, but I get empty as a result :

  • previous_nItems - Measure : previous_nItems = CALCULATE([nItems], FILTER(Table, Table[Date]=Table[previousDate]))

 

The next step I've in mind is to create a measure growth_nItems that would give me the growth of the items between two dates for all IDs :

  • growth_nItems = DIVIDE(nItems - previous_nItems, previous_nItems)

 

Table : 

IDItemsDate
a2510/10/2022
b1210/10/2022
c6710/10/2022
a3517/10/2022
b2717/10/2022
c8017/10/2022
a4522/10/2022
b3322/10/2022
c8622/10/2022


Could you please help me making previous_nItems working ?

1 ACCEPTED SOLUTION
MaskedNico
Regular Visitor

Hello,

 

Thank you @amitchandak   and  @v-yinliw-msft  for your answers.

 

I finally found an other solution that suited more my needs.

 

I created a calculated column previous_Items for storing Items of each ID at previsous date, as the formula and the table below describes :

 

 

previous_Items = 
VAR currentID = Table[ID] 
VAR currentDate = Table[Date] 
VAR previousDate = CALCULATE( 
    LASTDATE(Table[Date]),
    FILTER(Table, Table[Date] < currentDate) 
) 

RETURN CALCULATE( MAX(Table[Items]), 
    FILTER(Table, Table[Date] = previousDate), 
    FILTER(Table, Table[ID] = currentID)  
)

 

 

 

 

 

ID

Items

Date

prevous_Items

a

25

10/10/2022

12

b

12

10/10/2022

67

c

67

10/10/2022

 

a

35

17/10/2022

27

b

27

17/10/2022

80

c

80

17/10/2022

 

a

45

22/10/2022

33

b

33

22/10/2022

86

c

86

22/10/2022

 

 

I was then able to create the measure previous_nItems by summing the calculated column previous_Items

 

 

previous_nItems = CALCULATE(SUM(Table[previous_Item])) 

 

 

 

I also created a calculated column DoD_Items that calculates the growth of Items between two dates :

 

 

DoD_Items = DIVIDE(Table[Items] - Table[previous_Items], BMW[prevous_Items], 0) 

 

 

View solution in original post

3 REPLIES 3
MaskedNico
Regular Visitor

Hello,

 

Thank you @amitchandak   and  @v-yinliw-msft  for your answers.

 

I finally found an other solution that suited more my needs.

 

I created a calculated column previous_Items for storing Items of each ID at previsous date, as the formula and the table below describes :

 

 

previous_Items = 
VAR currentID = Table[ID] 
VAR currentDate = Table[Date] 
VAR previousDate = CALCULATE( 
    LASTDATE(Table[Date]),
    FILTER(Table, Table[Date] < currentDate) 
) 

RETURN CALCULATE( MAX(Table[Items]), 
    FILTER(Table, Table[Date] = previousDate), 
    FILTER(Table, Table[ID] = currentID)  
)

 

 

 

 

 

ID

Items

Date

prevous_Items

a

25

10/10/2022

12

b

12

10/10/2022

67

c

67

10/10/2022

 

a

35

17/10/2022

27

b

27

17/10/2022

80

c

80

17/10/2022

 

a

45

22/10/2022

33

b

33

22/10/2022

86

c

86

22/10/2022

 

 

I was then able to create the measure previous_nItems by summing the calculated column previous_Items

 

 

previous_nItems = CALCULATE(SUM(Table[previous_Item])) 

 

 

 

I also created a calculated column DoD_Items that calculates the growth of Items between two dates :

 

 

DoD_Items = DIVIDE(Table[Items] - Table[previous_Items], BMW[prevous_Items], 0) 

 

 

v-yinliw-msft
Community Support
Community Support

Hi @MaskedNico .

 

You can try this method:

vyinliwmsft_0-1666927473008.png

Here is Sample data.

Due to the Sample data, I create a date table:

vyinliwmsft_1-1666927591263.png

 

Then previous_nItems you can do like this:

Measure:

previous_nItems =
CALCULATE (
    SUM ( 'Table'[Items] ),
    FILTER (
        'Table',
        'Table'[Date]
            = DATE ( YEAR ( RELATED ( 'Table 2'[Date] ) ), MONTH ( RELATED ( 'Table 2'[Date] ) ), DAY ( RELATED ( 'Table 2'[Date] ) ) )
    )
)

If you want to know the previous data, you can select the previous date directly.

vyinliwmsft_3-1666931446235.png

 

Is this what you expect?

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@MaskedNico , Try a measure like

 

Last Day Non Continuous = CALCULATE(Countrows(Table) ,filter(ALLSELECTED('Date'),'Date'[Date] =calculate( max(Table[ Date]), FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])))))

 

Date of table and date of date table is joined

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.