Retrieving Sum from Prior Period

[Anonymous]

Hello all...

I'm looking for guidance on how to create a measure/measures that will retrieve the sum total from the prior period, in this case $4.00, that I can then use to subtract from the current period total, in this case $8.00. I will be inserting the difference between the 2 values in a table, sorted by project ID.

Thanks in advance to anyone who can help!

Project	Period	InsertedDate	Value
A	1	6/1/2020	$1.00
B	1	6/1/2020	$1.00
C	1	6/1/2020	$1.00
D	1	6/1/2020	$1.00
A	2	6/15/2020	$2.00
B	2	6/15/2020	$2.00
C	2	6/15/2020	$2.00
D	2	6/15/2020	$2.00

[Ashish_Mathur]

Hi,

Will the difference between 2 period always be 14 days?

[Anonymous]

Yes it should be.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur The code works for the periods that end on the 15th of the month, but not for the periods on the first of the month. The expression to produce a value for the previous period is returning blanks for the 1st of the month. Any ideas as to why?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  CLearly show the result that you are expecting in the blank column.

[Anonymous]

Thank you @Ashish_Mathur . That definitely gets me closer to where I want to be. I need to play around with it a bit more because what I need to see in the June 15 column is what I'm seeing in the Total column.

 

[amitchandak]

@Anonymous , Move period to separate table , say Period

and then try

This Period = CALCULATE(sum('Table'[Value]), FILTER(ALL('Period'),'Period'[Period]=max('Date'[Period])))
Last Period = CALCULATE(sum('Table'[Value]), FILTER(ALL('Period'),'Period'[Period]=max('Date'[Period])-1))

Very similar approach I used in Week here

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Anonymous]

@amitchandak  Thanks but I can't move the period to a separate table in this case.

[amitchandak]

@Anonymous , Try like

This Period = CALCULATE(sum('Table'[Value]), FILTER(ALLSELECTED('Table'),'Table'[Period]=max('Table'[Period])))
Last Period = CALCULATE(sum('Table'[Value]), FILTER(ALLSELECTED('Table'),'Table'[Period]=max('Table'[Period])-1))

[Anonymous]

Thanks, I'll try that.