Amount change from day to day

[Anonymous]

Hi All,

I am trying to create a column that displays the difference of the two columns next to it, which are displaying dollar totals, per account, per day. I want a third column in that white space, that shows the difference in each row

I have tried everything I can think of... which admittedly isn't much since I am new. Any thoughts are appreciated.

[parry2k · Accepted solution]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

From the date dimension, you can use time intelligence function and in this case, you can use the previousday or dateadd function to get previous day total and then get the difference

for example your measure will look like this

Base Measure = SUM ( Table[Amount] )

Diff from Prev Day = 
[Base Measure] - CALCULATE ( [Base Measure], DATEADD( DateTable[Date], -1, DAY ) )

OR

Diff from Prev Day = 
[Base Measure] - CALCULATE ( [Base Measure], PREVIOUSDAY ( DateTable[Date] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[v-juanli-msft]

Hi @Anonymous 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know. 

It is appreciated to share some examples to illustrate the data structure.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

parry2k provide the correct formula for difference calculation.

If your matrix is as below, you could create a measure to add the day difference beside the two columns.

Measure =
VAR diff =
    SUM ( 'Table 2'[value] )
        - CALCULATE ( SUM ( 'Table 2'[value] ), DATEADD ( 'Table 2'[Date], -1, DAY ) )
RETURN
    IF ( ISINSCOPE ( 'Table 2'[Date] ), SUM ( 'Table 2'[value] ), diff )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k · Accepted solution]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

From the date dimension, you can use time intelligence function and in this case, you can use the previousday or dateadd function to get previous day total and then get the difference

for example your measure will look like this

Base Measure = SUM ( Table[Amount] )

Diff from Prev Day = 
[Base Measure] - CALCULATE ( [Base Measure], DATEADD( DateTable[Date], -1, DAY ) )

OR

Diff from Prev Day = 
[Base Measure] - CALCULATE ( [Base Measure], PREVIOUSDAY ( DateTable[Date] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k @v-juanli-msft Thank you both for your replies. I am still unsuccessful but I think it is because I am omitting details that you need to know. I'll keep reading and will post back up if I figure out what I am not doing right.

[Anonymous]

Okay, I am going to try to explain what I did to get my result. It's probably pretty clunky, but if my boss is happy then I am happy:

I have a folder that Power BI looks into for a daily report that lists several accounts, and dollar balances in various stages of accounting. I have a visual that displays the stages we are interested in, and a slicer that allows us to look at the different days and what the balances were on those days.

I wanted to create a visual that compares the most recent two files, and if there were any changes in any of the accounts, display the differences. ("Hey Siri, show me how much money was spent last night, and which accounts?")

I created a new table that looks into the folder, and creates a column with the Source.Name, and starts an index from the number 1. It removes duplicates from Source.Name, and sorts in decending order. Then, I just created a measure that subtracts the dollar figures in index number 1 from index number 2. I display that measure in a visual, and filter out results that are net 0.

I am pretty sure that what you guys are doing is way easier, but for some reason I wasn't able to get the changes to work until I did the steps above instead. Does that make any sense? Sorry if this is painful to read, I am trying to teach myself how to use Power BI.

[v-juanli-msft]

Hi @Anonymous 

Glad you have worked with Power bi better.

If you have any further problem, feel free to ask in the community.

Best Regards

Maggie

[parry2k]

@Anonymous thanks for understanding, sometimes it is hard to think what you are actually working on, and because of lack of information, the solution doesn't work. Good luck and in case you run into issues, please reply here with more information, like what is not working and we can take it from there.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡