How to compute Aging for 1 to 31 Day ?

[admin11]

Hi All

My pivot table 1 is working fine :-

Due to when i export the above to excel the aging day sequences is not correct , i decide to change to stright table. my result as below , when i try to create first column amount for 1 to 31 Day :-

My expression :-

_Day 1 to 31 =

var _datediff=DATEDIFF(AR[AR Invoice Date],TODAY(),DAY)

return

SWITCH(

TRUE(),

_datediff<=31,"1 to 31 Day"

)

I need time above expression with field = AR_AMT_

Can some one share with me how to do it ?

Paul

[VijayP]

@admin11 

Find attached

[VijayP]

@admin11  Sharing in few minutes! creating for you!

[amitchandak]

@admin11 , Try like

_Day 1 to 31 Amount =
var _datediff=DATEDIFF(AR[AR Invoice Date],TODAY(),DAY)
return
SWITCH(
TRUE(),
_datediff<=31,AR_AMT_
)

or
calculate(sum(table[AR_AMT_]), filter(Table,[_Day 1 to 31 ] ="1 to 31 Day"))

[admin11]

@amitchandak 

I try both i get error :-

Try Measure

Try column :-

[admin11]

Hi All

https://www.dropbox.com/s/5hh00w7nwlcmnzi/PBT_V2021_171%20AR%201%20to%2030%20day%20amount.pbix?dl=0

My PBI sample file 

Paul

[VijayP · Accepted solution]

@admin11 

I Suggest you to watch this video to get AR Ageing with simpler approach

https://www.youtube.com/watch?v=3bV9BfB3_Oc

[admin11]

@VijayP 

I just notice that you are the one create the video

12k hv view your video 

May you should create more such video . For GL , Inventiry , Sales . 

or you can introduce me such kind of video very helpful 

[VijayP]

@admin11 

Sure, Iwish to create but need some sample data with a problem statement .

if you can explain the problem statements much clear regarding GL Inventory and Sale I could give a nice video, you can connect with me at LinkedIn 

[admin11]

@VijayP 

https://community.powerbi.com/t5/Desktop/Problem-Statement-Create-P-amp-L-Summary-Table-for-GL-ANALY...

Above link is the GL problem statement. i start with create a summary on P&L report. 

Hope you can contribute.

Paul

[VijayP]

@admin11 

Will Work on it! and share the solution soon!

[admin11]

@VijayP 

Look like i am stuck at how to create detail P&L report . So i create Filter report by P&L items. as below , So that i can view which are the expense are increase and decrease :-

Pls use this PBI file :-

https://www.dropbox.com/s/nc3jkl4tk7vhrkp/PBT%20GL%20VER0051%20GL%20ANALYSIS%20PROBLEM%20STATEMENT%2...

Paul

[admin11]

@VijayP 

https://www.dropbox.com/s/6kevhyu5kxt6epz/PBT%20GL%20VER0033%20GL%20ANALYSIS%20PROBLEM%20STATEMENT%2...

Pls use above PBI file. As it have Net profit amount.

Paul

[admin11]

@VijayP 

Below PBI file have solve the Sorting of P&L field :-

https://www.dropbox.com/s/rq165gc961b79hq/PBT%20GL%20VER0032%20GL%20ANALYSIS%20PROBLEM%20STATEMENT%2...

I hope you can help me look into Problem statement 4. Create a detail P&L report. As look like this is one of the most diffcuilt part :-

https://community.powerbi.com/t5/Desktop/Problem-Statement-4-How-to-create-a-detail-P-amp-L-Statemen...

Paul

[VijayP]

@admin11 

That is the Easy part! only thing we need to have clarity about the Data!

I have gone thru the Data , let me know one thing

There are few Expense Items like Provision for Depriciation , Output Tax % and few more don't have the Expense  Category, even not as Others, I need to understand whether those are part of expenses or to be shown in Balance Sheet!?

[admin11]

@VijayP 

May be it is due to the PBI i share with you , i have filter off some row. 

Can you using below PBI file , i load with full data :-

https://www.dropbox.com/s/kwuwkpmysxdo5nl/PBT%20GL%20VER0057%20GL%20ANALYSIS%20PROBLEM%20STATEMENT%2...

Let me know which expense data you need to know more info.

Paul

[admin11]

@VijayP 

I have 4 problem statement in below PBI file :-

https://community.powerbi.com/t5/Desktop/Problem-Statement-3-on-GL-Analysis-Sort-P-amp-L-Field-in/m-...

Paul Yeo

[admin11]

@VijayP 

Yes this video is very good. firstly I get to learn how to create those Aging expression and report can drill down . 

Do you have this video PBIX file ? 

[VijayP]

@admin11 

Please let me know whether that can be your required Solution and if so Please mark the same and also Kudos are appreciated