Calculation of Previous day sales minus todays sales

[Anonymous]

Hi Team,

I have a data in below table : 

As of Date	Sales
1/12/2018 12:33	234
1/12/2018 12:45	340
1/13/2018 12:33	220
1/14/2018 12:33	350
1/15/2018 12:33	450
1/15/2018 12:45	480

I am expeting result like this : - 

As of Date	Todays Sales	Yesterday Sales	Profit/Loss
1/15/2018 12:45	480	350	130
1/14/2018 12:33	350	220	130
1/13/2018 12:33	220	340	-120
1/12/2018 12:45	340

Could you please help me with this ??

Thanks,

Shubham

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Would you refer to the steps below:

1.Create a calculated column for the table to make it easier to create the measures:

Date1 = 'Table'[As of Date ].[Date]

2.Create three measures for your expecting output:

Today sales =

VAR a =

    CALCULATE (

        MAX ( 'Table'[Sales] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Date1] IN DISTINCT ( 'Table'[Date1] ) )

    )

RETURN

    IF ( MAX ( 'Table'[Sales] ) = a, a, BLANK () )



Yesterday Sales =

IF (

    'Table'[Today sales] = BLANK (),

    BLANK (),

    CALCULATE (

        MAX ( 'Table'[Sales] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Date1] = MAX ( 'Table'[Date1] ) - 1 )

    )

)



Profit/Loss =

IF ( [Yesterday Sales] = BLANK (), BLANK (), [Today sales] - [Yesterday Sales] )

Please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETQYYmERrR9BvaaxL0rx7QUBjyYI3dpM-ggszZRGSsEu_Q?e=itfMF2

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

Best Regards,

Dedmon Dai

[Greg_Deckler]

So, table visualization with your date in the first column and then these measures:

Todays Sales = SUM('Table'[Sales])

Yesterday Sales = 
  VAR __CurrentDate = MAX('Table'[As of Date])
  VAR __YesterdaysDate = DATE(YEAR(__CurrentDate),MONTH(__CurrentDate),DAY(__CurrentDate)) - 1
RETURN
  SUMX(FILTER(ALL('Table'),DATE(YEAR([As of Date]),MONTH([As of Date]),DAY([As of Date])) = __YesterdaysDate),[Sales])

Profit/Loss = [Todays Sales] - [Yesterday Sales]

If you can ditch the time component in your date/time this would be far simpler.

[SimoneXasto]

Hi @Greg_Deckler I am trying to use your formula but if I write the minus 1 my date becomes wrong and goes back to the year 1899. Do you have any idea of why?

Cheers

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[SimoneXasto]

Hi Ashish,

I have a list of dates with some prices for example:

dd/mm/yyyy     price

1/1/2021         22

2/1/2021         19

3/1/2021         25

4/1/2021         27

My end goal is to calculate the following formula:

(today price - day before price)/day before price x 100

I am having trouble getting the day before price. I tried to implement Greg's formula above but I get a weird result, meaning that the date i get back is from 1899 with wrong day and month too. The formula that I am writing is:

Yesterday Adj Close price =

VAR _date = MAX(Holdings_NEW[Date])

VAR _Yestedaydate = DATE(YEAR(_date),MONTH(_date),DAY(_date)) -1

RETURN

_Yestedaydate

[Ashish_Mathur]

Hi,

Create a Calendar Table.  Build a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Date from the Calendar Table.  Write these measures

P = sum(Data[Price])

P on previous day = calculate([p],previousday(calendar[date]))

Measure = divide(([p]-[P on previous day]),[P on previous day])

Format Measure as a %.

Hope this helps.