Seeking a dax formula

[Anonymous]

Hi,

I'm seeking for a DAX formula who is gonna give me 

(price today - (price yesterday)) / (price yesterday)

and i aso want to see how to see if the prices jump over lets say 10%

What kind of formula can I use in DAX for this? 

Br, 

[AntrikshSharma]

@Anonymous  Try something like this:

% Change over time =
VAR CurrentSales = [Total Sales]
VAR PreviousDaySales =
    CALCULATE ( [Total Sales], PREVIOUSDAY ( Dates[Date] ) )
VAR PreviousMonthSales =
    CALCULATE ( [Total Sales], PREVIOUSMONTH ( Dates[Date] ) )
VAR PreviousYearSales =
    CALCULATE ( [Total Sales], PREVIOUSYEAR ( Dates[Date] ) )
VAR Result =
    IF (
        ISINSCOPE ( Dates[Date] ),
        DIVIDE ( CurrentSales - PreviousDaySales, PreviousDaySales ),
        IF (
            ISINSCOPE ( Dates[Month] ),
            DIVIDE ( CurrentSales - PreviousMonthSales, PreviousMonthSales ),
            IF (
                ISINSCOPE ( Dates[Calendar Year Number] ),
                DIVIDE ( CurrentSales - PreviousYearSales, PreviousYearSales )
            )
        )
    )
RETURN
    Result

 

[Fowmy]

@Anonymous 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@Anonymous , you can have following formula as a measure for this date vs last day using date table

example

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Last Day = CALCULATE(sum('order'[Price]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Price]), previousday('Date'[Date]))

This Day = CALCULATE(sum('order'[Price]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

Diff% = divide([This Day] -[Last day],[Last Day])

This link has column method too

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

if date is not Continuous 

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandak  Hi thank you sir.

Just one stupid question, where do I write all this? Not in the 'add colomn' right? like this? where there is the yellow part here (ps. this pic is taken from google)

[vivran22]

Hello @Anonymous ,

All the solutions suggested are measures(Right-click on a table > New Measure).

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter 

[harshnathani]

Hi @Anonymous ,

You cna have a look at this blog incase you want difference between consective orders.

https://community.powerbi.com/t5/Community-Blog/Getting-Previous-Values-in-Power-BI-Part-1/ba-p/1143370

Regards,

Harsh Nathani

[vikrantarora25]

Hey Meha,

Assuming your table is like: (If not like below then pls share structure of your table)

Date         Price

11-Aug    80

12-Aug     100

13-Aug     125

To calculate %Change in Price first add an index column using query editor, its very simple 

Then add a calculated column in your table using below formula, Lookupvalue is used to retierve todays & yesterdyas price and then difference between the two is divided by yesterdays price. IF is used so put a blank in the %change column for the 1st date of the table.

Table[% Change in price] = IF (
    Table[Index] = 1,
    BLANK(),  //for first date we show as blank
   DIVIDE(LOOKUPVALUE ( Table[Price], Table1[Index], Table1[Index] ) //gives today's price
        - LOOKUPVALUE ( Table[Price], Table1[Index], Table1[Index] - 1 ) //gives yesterday's price, 
     LOOKUPVALUE ( Table[Price], Table1[Index], Table1[Index] - 1 ))
)

To show the result as %, selected the calculated column in data view and change the format to percentage. And to see above 10% changes you may use filter in data view or is you have a matrix or a table visual then use conditional formatting.

Feel free to ask fopllow up questions andif this solves your query please mark this as a accepted solution.

Regards

Vikrant