Get the sum at the previous date with non continuous dates

[MaskedNico]

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 : 

ID	Items	Date
a	25	10/10/2022
b	12	10/10/2022
c	67	10/10/2022
a	35	17/10/2022
b	27	17/10/2022
c	80	17/10/2022
a	45	22/10/2022
b	33	22/10/2022
c	86	22/10/2022

Could you please help me making previous_nItems working ?

[MaskedNico · Accepted solution]

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]

Hi @MaskedNico .

You can try this method:

Here is Sample data.

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

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.

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]

@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