Latest Enterprise DNA Initiatives

Cash Flow reporting--Help! Help!

Data Model
#1

My goal is to create month wise Cashflow report.

As can be seen from attached sheet, in Table BS, there are 2 flags for same ID in Oct and Nov.
In Table A, I need to lookup the flags from Table BS ,
ie.
In my Table A , in Oct , I want “Matched” flag
and in Nov , I want “Reversal” flag
So that corresponding logics are applied for each month

What can be done? Is anything in data missing or can be done in PBI?

In the end, I should have something like this:

Oct Nov

X Amount Y amount (using “Reversal logic” from table A)
(using “Matched” logic from table A )

#2

basically what i want is , i have a dataset A which gets appended every month. The amount from this needs to be compared against an amount in a constant dataset B to create a flag every month. And using this flag, i need to apply some logics to the constant dataset B every month. The results of this logic should be stored for every month .

Should I be creating copies of the constant Dataset B every month? If so how?

how can this be done in BI

#4

I’ve checked out the excel data, but I must say it’s hard to really know how to give a solid answer.

To get proper assistance my recommendation is to get something into Power BI so that it can be looked at in the right environment. And then also the details you mention will make a lot more sense.

Also if you want to create any financial statement, there is an entire course covering this material which will give you a lot of the techniques to use to solve for this.

See below

Also see here for a specific section just on cash flow reporting ideas

I really recommend going through this entire course though. You will learn so much about how to deal with financial data in Power BI.

Thanks
Sam

#5

I have attached the sample pbix,
Here I am using Calculated Tables to do the comparison of Discoverer Amount and AP Amount,
and to Create Flag.
(I have not created any data model behind since its many to many relationship. And we are doing data consolidation here.)
Based on this flag ,say, if “Matched” in OCT ,I need to find the corresponding rows in AP table which are matched. And corresponding to those rows, i need to lookup values from another table.
If "Reversal " in November, I need to do find the corresponding rows in AP table,and do another set logics for those rows

#6

I have already gone through your financial reporting module.
But the setup is different here.
The categories are coming from the data itself and not excel templates.
So I just have to consolidate data from different sources

#7

If I understand correctly, you need to create a unique table that has the unique ID from the two tables, link this distinct table to both tables, and then create a measure to use from table A, another measure from table B.
Once you have all in one visual, create another measure for your flag, if difference is zero then matched, else Reversal.

Thanks.

#8

The problem I am facing is corresponding to same concatenation, there are two flags in the summary table( pbix attached).
I want to lookup the flags from the summary table** in AP table**.
Then I want to find all the rows in AP table where flag is “matched” and flag is “reversal”.(AP table is more granular than Discoverer table.)
For the matched rows in AP table, i need to create a column static amount =amount from AP table
For the reversed rows in AP table, i need to create a column static amount = -Amount from AP table

#9

Have a look. Sample Pbix.pbix (102.9 KB)

#10

I need to compare Discoverer Amount against AP amount,not vice versa.
For each month, I should compare what is the discoverer amount for a particular concatenation against the AP amount for the same concatenation(In AP without date)

Also, if i create flag as measure,
How do I filter the AP table.
I want to do FILTER(AP table,Flag= “Matched”

I wont be able to do the same with a measure,right?
FILTER(AP table, Flag_Measure =“Matched”) won’t return the same results.

That is why I went with calculated column. Correct me if wrong

#11

We always avoid the calculated column as much as we can. working with measure is always better.
Just click on the matched records and the AP table visual will be filtered automatically.
All the best.

#12

I need to compare Discoverer Amount against AP amount,not vice versa.
For each month, I should compare what is the discoverer amount for a particular concatenation against the AP amount for the same concatenation(In AP without date)

#13

I dont need this in visual. I am doing some data consolidation.
So I need to filter the AP table having Flag = Matched

#14

I hope someone else can help you but you can still export the visual to excel if you like.

#16

Hi @Anu, Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum