ONLY FOR PBIX, DAX MASTERS! :) -> sum previous row value + current row value and merge it

[ph]

Hi all,

I have table with following values

Date	Document type	Amount
01.01.2019	1	10
02.01.2019	2	20
03.01.2019	3	30
06.01.2019	4	10
09.01.2019	2	20
12.01.2019	3	30

Each document type has some spaces in date (4,5,7,8,10,11 etc.. of Jan 2019 is missing, that's correct), but dimension DATE contains all values in year on each row, but document types not..

I need to merge document types with each day in year for document type (1,2,3) and count previous value with current of the result of the day.. result should be this:

Date	Amount
01.01.2019	10
02.01.2019	30
03.01.2019	60
04.01.2019	60
05.01.2019	60
06.01.2019	60
07.01.2019	60
08.01.2019	60
09.01.2019	80
10.01.2019	80
11.01.2019	80
12.01.2019	110

I created calculated column in dimension DATE (because it contains all values), but I don know how to do SUM of previous row with current ROW...I created a measure where I sum all document types
(Total Amount= Measure 1 + Measure 2 +Measure 3)
         where
         Measure 1=

         CALCULATE(SUMX(Document; [Amount]);'Document Type'[Document Type]=1)

         
         Measure 2=

         CALCULATE(SUMX(Document; [Amount]);'Document Type'[Document Type]=2)
         
         Measure 3=

         CALCULATE(SUMX(Document; [Amount]);'Document Type'[Document Type]=3)

         and I tried used 'earlier' in function in column
         Total Amount=Total Amount+earlier(Total Amount)

         ,but it does not works

[Anonymous]

Use Power Query for this. DAX is not the right tool for this kind of job. But if you like squeezing a square peg into a round hole... then DAX is the way to go 🙂

Best
Darek

[ph]

@Anonymous OK..but my question is the same...how to do it?
in power query I don t see any measures etc..

---

[Anonymous]

OneDrive: How to create a running total for each day by document type

Best

D.

[ph]

Hi @Anonymous

many thanks for solution! It is what I need it..but is it possible to write here any comments? I want to implemented to my report and absolutely no idea how and what you set up..I see that steps on the right side, but it is very high level

[Anonymous · Accepted solution]

The OriginalData table is what you should import from your data source. The other tables were created using OriginalData as the source.

OriginalData should have columns Date, DocType, Amount. In truth, they could have any names. It's just a matter of adjusting the names in the M code.

The thing is you should first import your Original Data and then create the Calendar as I have in the file and then create the ProcessedData table that uses both: the Calendar and OriginalData.

All the steps are there in the M code.

If you import your table into the file I gave you, delete the OriginalData and rename your imported table to OriginalData (and the columns will be the same with the same names as the table you've just deleted) the other tables will be there ready for you to use immediately.

Best
D.

[ph]

@Anonymous 

thanks for it

I also found an article how to do it in DAX and its works (https://joyfulcraftsmen.com/blog/dax---cumulative-total-and-blank-handling/)

🙂

[Anonymous]

Good advice: you should never do in DAX what you can do in Power Query if you want your model to run at the peak speed. Columns calculated in DAX are never compressed as well as the ones that come from Power Query. And compression plays a big role when the storage engine is calculating things for you in DAX.

Best
D.

[Anonymous]

Bear with me a sec... I'm creating a solution. By the way, this stuff is best done in Power Query, not in DAX.

Best
D.