Calculate difference from last update date

[Anonymous]

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.

[Icey]

Hi @Anonymous ,

Why not try to create a measure using DAX?🤔

Something like so: 

Find a Value in the Previous Row | Burningsuit;

Previous Dynamic Period DAX Calculation - RADACAD;

Creating a Power BI Chart Comparing Each Year vs Previous Year - Carl de Souza.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

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]

@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.

[smpa01]

@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