Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculate difference from last update date

My sales data grouped by week is updated weekly, for confirmed orders and projected orders. 

The table includes [Update Date], [Sales Date], [Bill Type], [Total Qty] and [Total Rev].
I would like to know the difference in sales in each week between the current date and the last update date.
I was using SQL to calculate the difference before migrating to Power BI, can anyone suggest how to prepare the data and have the data in a chart?

This is the table I have


and this is the script used in SQL.

 

SELECT distinct

t1.[ Sales Date],t1.[Update Date],t2.[ Sales Date],t2.[Update Date] , t1.[Projected Qty],t1.[Confirmed Qty],

(t1.[Projected Qty] - t2.[Projected Qty]) as [Diff Projected Qty],

(t1.[Confirmed Qty] - t2.[Confirmed Qty]) as [Diff Confirmed Qty],

t1.[Projected Rev],t1.[Confirmed Rev],

(t1.[Projected Rev] - t2.[Projected Rev]) as [Diff Projected Rev],

(t1.[Confirmed Rev] - t2.[Confirmed Rev]) as [Diff Confirmed Rev] from

(select t.*, rankdenseasc(t.[Sales Date],t.[Update Date]) as RankId_1 from [Diff custom]t)t1

left join (select t.*, rankdenseasc(t.[ Sales Date],t.[Update Date]) as RankId_2 from [Diff custom]t)t2

on t1.[ Sales Date]=t2.[ Sales Date] and

t1.RankID_1=(t2.RankId_2)-1

 

 

I would appreciate all the solutions.

Need Help

4 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    Anonymousif you want o keep on utilizing the same SQL script in Power BI to get to the output, you can do that by clicking on Home>Get Data>SQL Server

    Fillup Server name

    Fill up Database,

    Click on Advanced Opeions and paste the query in SQL statement

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi smpa01 

    The problem is my data is not in SQL server but stored in SharePoint. I don't think I can use SQL script in this way, correct？

    • smpa01's avatar
      smpa01
      Community Champion
      5 years ago

      Anonymousokay. If this is a sharepoint location, I dont think you can utilize the SQL script on a sharepoint file. On a sharepoint list, you can utilize some limited sql scripting if you connect it through sharepoint rest api. However, if you can connect to this data source through ODBC connector, you can probably utilize the full sql script. If none of the above works this whole sql query can be converted into a fully working power query.