CODEWITHSUNDEEP
powerbi
Imran
Imran

Reputation: 1

Power BI - Daily difference as a value and percentage

I have a power BI report that has the following

Date, City, Town, Order number,

What i would like to do is create a report that shows the total orders (volume) for every day (which i can do as that is nice and easy) but I also wanted to show the difference from the previous reported day (some days we dont have data, eg bank holidays etc)

I am new to power bi and my technical skills are not brilliant.

Thank you in advance to anyone who is able to provide a solution.

Upvotes: 0

Views: 3019

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

Welcome to SO. There are a few ways of achieving it - you can even calculate these values directly in Power Query - it all depends on your data model and how the report itself is constructed.

Below are two solutions that you might want to consider:

Solution 1 - calculated column

This adds a new column to your table. The overall concept is to find the maximum date that is less than the current row's date and retrieve the respective value.

Volume t-1 =
var ThisDate = Table1[Date]
var PrevDate =
    MAXX(FILTER(ALL(Table1[Date]), Table1[Date] < ThisDate), Table1[Date])
var PrevValue =
    MAXX(FILTER(Table1, Table1[Date] = PrevDate), Table1[Current Volume])
return
    PrevValue

You can now use this new column to calculate the difference between the current value and the previous value, e.g.:

Difference = [Current Volume] - [Volume t-1]

Solution 2 - measure

mVolume t-1 =
var ThisDate = MAX(Table1[Date])
var PrevDate =
    MAXX(FILTER(ALL(Table1[Date]), Table1[Date] < ThisDate), Table1[Date])    
var PrevValue =
    MAXX(FILTER(ALL(Table1), Table1[Date] = PrevDate), Table1[Current Volume])
return
    PrevValue

Similar to the first solution, you can now calculate the difference between this measure and the [Current Volume] field. However, the final formula will depend on your report and visualization filters. For example, if you add a table with Dates column (daily frequency), you can add the following measure to your table visualization:

[mDifference] = MAX(Table1[Current Volume]) - MAX(Table1[Volume t-1])

I hope this is a good starting point - good luck!

Upvotes: 1

Related Questions