dashboad visualization for both profit & loss + balance sheet items

[Anonymous]

hi,

i need help to create dashboard(s) that contain visuals for both profit & loss (with cummulative balance) & balance sheet (with balance on specified point in time) on the same page(s). currently, i'm having problem with balance sheet items that are also showing cummulative balance (instead of the balance at a specific point in time) when displayed together with profit & loss items especially & filtering by date.

currently, both profit & loss & balance sheet values are residing in 1 table while measures for them are residing in a different table & there's no relationship between them.

i'm not sure if if this has anything to do with custom visual interaction configuration for profit & loss and balance sheet visuals or tweaking of dax measures specific for profit & loss & balance sheet items.

appreciate any help.

tks & krgds, -nik

[amitchandak]

The information you have provided is not making the problem clear to me.

Hope you have date table and created cumulative like this

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

many thanks for your reply, @amitchandak.

to kindly clarify abit more, in accounting - profit & loss items (e.g. expenses or income) should have cummulative total if we filter, for example, between 01jan19 - 31mar19. however, for balance sheet items (e.g. assets or liabilities), the total amount should be the amount as of that point of time (e.g. as of 31mar19). 

for example,
a. manpower expense (profit & loss item)

    period                      amount      profit & loss total
    01jan19-31jan19      $1,000        $1,000      

    01feb19-28feb19     $1,500        $2,500

    01mar19-31mar19   $500           $3,000

b. cash (balance sheet item)

    period                      amount      balance sheet total
    01jan19-31jan19      $3,000        $3,000      

    01feb19-28feb19     $3,500        $3,500

    01mar19-31mar19   $3,000        $3,000

i need to show that the balance sheet (non-cummulative) totals are as per example in cash totals above for each month.

i'll try the dax formula you have kindly suggested below although i think it's probably applicable for profit & loss items only for (for cummulative totals).

tks & krgds, -nik

[Anonymous]

pursuant to my reply to you ( @amitchandak), i believe your solution seems to also be applicable to balance sheet.

however, i have another issue i.e. if we were to use card visual to show balance sheet totals for a specified period (e.g. 31mar19), can that be done too? for cash on 31mar19, can the card show $1,000, $3,500 & $3,000 for jan19, feb19, & mar19 respectively?

tks & krgds, -nik 

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

however, i have another issue i.e. if we were to use card visual to show balance sheet totals for a specified period (e.g. 31mar19), can that be done too? for cash on 31mar19, can the card show $1,000, $3,500 & $3,000 for jan19, feb19, & mar19 respectively?

For your requirement, you need to use Multi-row Card visual to get your requirement, drag [period] into visual too.

Regards,

Lin

[Anonymous]

Tks, @v-lili6-msft.

I am considering that together with other visual options as required by a client. I gather that each visuals also have different capabilities/limitations in displaying the different type of measures that you have created.

Kind regards, -Nik