Logic re switcher

Power BI
1

Hello friends,
I am going to utilize the methods from the course.

Additionally I want to add a switcher: cash basis revenue and accrual basis revenue.
Cash basis revenue contains in the table with expenses (transaction table). There is no need to change expenses re accounting method. Accrued revenue is in a separate table. All tables have relationships via Chart of Accounts table.

So I need to change only revenue using the switcher . My question is about appending. Sam made one PL table data in the course. Do I need to append accrual revenue to the transaction table?

Could you please advise me about the logic re switcher? I guess I need something with Switch function, something similar to the logic from the course: one measure like in the course and another is with accrued revenue ? But how to implement this? I would be grateful for any ideas on this matter.

Best,

2

Hi @Pavel, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi @Pavel

It’s a bit difficult to provide Solution without looking into Actual data. What I have tried is creating two table as Cash Revenue and Accrual Revenue.

Step 1 - Union these two to create Channel Revenue table. Diff Category based on Table.

Channel Revenues =
Var CashRev = SUMMARIZE( ‘Cash Sales’, ‘Cash Sales’[Channel], Dates[Month & Year],
“Category”, “Cash Revenue”,
“First Date”, MIN( Dates[Date] ),
“Sales Values”, [Total Sales cash] )

Var AccRev = SUMMARIZE( ‘Accural Sales’, ‘Accural Sales’[Channel], Dates[Month & Year],
“Category”, “Accural Revenue”,
“First Date”, MIN( Dates[Date] ),
“Sales Values”, [Total Sales Accural] )

return
union(CashRev,AccRev)

Step 2 - Created Index statement. Same as existing.

Income Statement = 
UNION( 'Company Expenses',
    SUMMARIZE( 'Channel Revenues', 'Channel Revenues'[Channel], 'Channel Revenues'[Category], 'Channel Revenues'[First Date], 'Channel Revenues'[Sales Values] ) )

Step 3 - Create Revenue in two ways. Create two separate Measures for Cash and Accrual OR
create a Slicer and based on Selection, change Revenue Dynamically.

Revenues Cash= 
CALCULATE( [Financial Values], 'Income Statement'[Category] = "Cash Revenue" ) OR 
Revenues Accrual = 
CALCULATE( [Financial Values], 'Income Statement'[Category] = "Accrual Revenue" )

OR

Revenues Dynamic =

var SelectedTable = SELECTEDVALUE(TableSelect[Selection])
var CashRev = CALCULATE( [Financial Values], ‘Income Statement’[Category] = “Cash Revenue” )
var AccRev = CALCULATE( [Financial Values], ‘Income Statement’[Category] = “Accrual Revenue” )

return

switch(SelectedTable,“Cash”,CashRev,AccRev)

Step 4 - Use new Revenue Measures in Final Measure.

Selected Year Actuals = 
VAR CurrentItem = SELECTEDVALUE( 'Income Statement Template'[Items - Normalized] )

RETURN
SWITCH( TRUE(),
    CurrentItem = "Total Revenues", DIVIDE( [Revenue Dynamic], 1000, 0 )

Above need to be repeated for all required measures. Check if this is helpful. If any issues, let us know.

Attached the PBIX. Check Income Statement Page.Financial Reporting In Power BI.pbix (862.3 KB)

Thanks
Ankit J

3 Likes
4

Hi @Pavel, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!