YTD in accounting entries and sum of inventory entries from Since inception

DAX DAX Calculations
1

I am developing financial report Profit & Loss Statement.
In this, have to show summary of Accouting entries and Inventory entries.
I have done Union of Accounting & Inventory entries as per below table

PL = UNION(
SELECTCOLUMNS(InventoryClosing,"Company",InventoryClosing[Company],"Amount",CONVERT(InventoryClosing[Amount],double),"GLName",InventoryClosing[GLAccountName],"Category",                            InventoryClosing[Category],"SubCateogry",InventoryClosing[SubCategory],"Amt",        InventoryClosing[Amount],"PostingDate",InventoryClosing[PostingDate]),
            SELECTCOLUMNS(InventoryOpening,"Company",InventoryOpening[Company],"Amount",CONVERT(InventoryOpening[Amount],Double),"GLName",InventoryOpening[GLAccountName],"Category",InventoryOpening[Category],"SubCateogry",InventoryOpening[SubCategory],"Amt",InventoryOpening[Amount],"PostingDate",DATEADD((InventoryOpening[PostingDate]),0,YEAR)),
SELECTCOLUMNS(vw_PL1,"Company",vw_PL1[Company],"Amount",vw_PL1[Amount]*-1,"GLName",vw_PL1[GLAccountName],"Category",vw_PL1[Category],"SubCateogry",vw_PL1[SubCategory],"Amt",vw_PL1[Amount],"PostingDate",DATEADD((vw_PL1[PostingDate]),0,YEAR)))

Created the following measure on PL table

FYTD Measure = TOTALYTD ( SUM ( PL[Amount] ), ‘Date’[Date], “03/31” )

PFYTD MEASURE = CALCULATE ( [FYTD Measure], DATEADD ( ‘Date’[Date], - 1, YEAR ) )

FQTD Measure = TOTALQTD( SUM ( PL[Amount] ), ‘Date’[Date])
PFQTD MEASURE = CALCULATE ( [FQTD Measure], DATEADD ( ‘Date’[Date], - 1, YEAR ) )

This report show data on YTD & QTD basis but i need sum value of inventory
entries from begining till date and accounting entries should be on basis
of YTD or QTD, need you help in this DAX formula.
Account Name of Inventory entries is different and fixed from Accounting entries.

2

rajendermakhija,

Do you have a pbix sample of exactly what you are trying to achieve?

Thanks,
Mark

3

BS&PL.pbix (2.8 MB)

Hi Mark,

Kindly find the attached PBIX.

Regards,

Rajender

4

Hi @rajendermakhija,

Thanks for sharing pbix file. I am sorry that I am not accountant, so I could not completely understood about your requirements. What I understood is that you need accumulated total of Inventory value and not YTD/QTD. Please see if following formula works for you.

Inventory(Cummulative Total) =
CALCULATE (
    SUM ( 'vw_InventoryValue'[Amount] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'vw_InventoryValue'[PostingDate] )
    )
)

Also, why don’t you have relationship between vw_InventoryValue’ and Date table?

image
image.png463×788 44.1 KB

5

Hi Hafiz,

That will do the calculation but i have to show Accounting and Inventory values in single column of Matrix Control, for this have made new table PL which is union of PL1(Accounting Entries) and Inventory Entries(Opening Inventory + Closing Inventory) and PL table have join with Date table. Can i put condition in "FYTD measure " that if value in Row column is Inventory Account than do accumulated Total other wise do TotalYTD/QTD.

Thanks
Rajender

6

Thanks for posting your question @rajendermakhija To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; 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.

Including all of the above will likely enable a quick solution to your question.

7

PL Report.xlsx (15.2 KB)

Hi,

Please find the attached sample of Excel which want to create in power bi.

Regards,

Rajender

8

Hi @rajendermakhija, Have you gone through this course here? - https://forum.enterprisedna.co/t/course-introduction-financial-reporting-w-power-bi/3421

9

Hi @rajendermakhija, we’ve noticed that no response has been received from you since the 3rd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. 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 checkbox. Thanks!