@@vijay_ I wanted to count employee by age groups. My model has FACT rows populated by month for EoM metrics. One very silly mistake I was doing is that I calculated age in my source excel and assumed it was numeric. DAX showed error and I struggled for some time before I realized that the field should be defined numeric or whole number. The second challenge was to ensure that for the selected FY filter, I needed the latest set of metrics and here is where I used a Variable to get MAX action date and included the same in my filter. Pretty satisfied with my DAX measure now as it can help create multiple analysis around these age groups.
@@krishnanseshamanikrishnan1950 not really required to send bulk email. Once you publish the report to power bi service and share with team members. They can use this report , but will th pro license. And subscribe all the use s to the report thru will get an email with ageing report on the e-mail body without any bulk mail mail setup
Learnt heaps, but for me one of the most powerful pieces of information was when you referred to the number of closing parenthesis and how you know you have included the right amount was when the tool tips box disappeared (@8:25) - never realized that this was a great visual tip for having the correct number of closing parenthesis - so simple!
The formula is not working with me and I don't know why, I have tried to track your steps with no success Inv 120-360 = CALCULATE([Total Invoices],FILTER(ALL('الجدول1'[AGE],'الجدول1'[AGE]>=120&&'الجدول1'[AGE]
It evaluates Each individual age rows to make sure the number rows more than 0 . Based on that the data will be populated on the context filter with other age group table.
Hello Vijay. Great video as it helped me a lot. One question I have is, why is the age grouping done as 0-30, 30-60, 60-90 instead of 0-30, 31-60, 61-90 and so on? Thanks in advance.
Thanks for the great tutorial. Can you please also give some more insights for historical aging accounts receivable amounts for the selected date in the past? Say, I want to compare last months aging AR to current month or week. How to built in the paid amount or paid date into formula?
@@vijay_ Thanks Vijay. Can you create a video on your channel and show that based on your database? I found there were a lot of people asking same question as mine, but there are no tutorials on aging AR historical trends.
In the source data you can create two columns one payment received and another one is balance Ostanding. Use balance Ostanding column for your calculations to hv control on the as on date value.
Hello Vijay Sir. I just came across this vdo. Are there more vdos on AR related please? I am looking for Weighted average days by cust calculation please.
This was very helpful. Thank you. I'm trying to figure out how to add retainage to the groups and basic measures. In my table I have a field for amount due and a field for retainage due. How can I get the retainage due to show in my visuals? I need the total retainage due not based on aging.
Vijay, Good Video. Is there a way to link your source table (Debtors) to your group table (Groups)? I am trying to cross filter a table consisting with all of my source data with my aging groups and there are no relationships established between the two tables. I tried creating a column for today's date however this Many/Many relationship is not allowing the results to cross filter. Thank you,
Hi Alec, Let me look into and create a model so that I can explain you. Give me little time. Mean time watch my latest video on M query and let me know your views Thanks
Good video ... Simple explanation . Two questions with practical perspective1 This is an as on date report what about if we want to have ageing as on specific date . 2 what about payment against your invoice , how would you handle this if the data comes from erp directly. considering as on date would be simpler but again how do you manage as on specific date . There will be application against each invoice ?
If the Data comes from the server, you can take the data (only Outstanding) to make the logic used in the Video possible. We need to run an SQL Query. Yes we can create on a specific Date context also using DAX.
Then add a new column containing a current month day 1 With = 1 & FORMAT(TODAY (),,"MMM")&YEAR(TODAY ())) And convert the data type to date Age in days = DATEDIFF( invoice date,new column , Day), will give you the required result
Could you pls create a inventory aging Report for given period of time with slots(0-90,91-120,121-181...) With inventory and Value of these buckets/slots
Many thanks for the video. Its been very useful. I have a question on one of the measures ie the Invoice 120-360 days. where do I insert a ZERO number in the formula in order to ensure that I don't get a (Blank). I inserted a ZERO at the end of the measure and got an error
Hi Sir, I want to learn Power Bi can u suggest how to learn. One on one have been given a project by my company. Trying online videos but its not helping me too much.
It is always better to get trained by a professional trainer. You can visit few best institutions in you area to find a trainer. You tube videos are basically one time reference points
Great video. Just quick question, do you know why the Age groups are not following the specific order I gave them? In your video they do. But I have 0-30, then 60-90, etc. when I do a pivot/filter table.
You can customise it the way you want But need to make sure there is no gap. In your case 0-30, and later it was 60-90., Between these two all records are not getting evaluated being nin considered in any group, do you must hv consider them in any one of the age group. Hope that clarifies
Thank you so much for the video. It is very useful. I did it for collection by AR Ageing (Invoice date - Received date). When I add filter "Month" in the page, it is ok if we look by month. But I have a problem when I need to see by YTD, I have created Collection YTD by AR Ageing . The result still same MTD. Do you know what are measures I have missed.
Thanks for this video! One question, what is the advantage to doing this data coding in powerbi compared to directly in excel? For example, would this have the same outcome if the groups were already added in the excel sheet?
Hello Vijay This has been a great help in solvin gone of my most complex scenarios. However, I need to have the report as at a specific date in the past based on a filter on calendar date table, hence the age column will be dynamic. Can you please provide some guidance? Thanks
Please I have this question,sir: 1..how do I get the outstanding before doing their ageing analysis if I am not using any ERP as your fact sheet was pulled from an ERP (assumed)? 2. If you use an ERP and you pull for the outstanding before doing ageing analysis, may I know how your fact sheet will get updated with new data? Thank you
hello, sir, I wanna do an analysis of one Fiscal year balance sheet where we have columns of opening , debt, credit, closing. please suggest me a video to do this properly
@@SM-vd1ri Hi, did you find out how to do that? I can't create the age based on the selected date, the calendar table is not active to bring to the formula..
@@shubhamsharma8373 Try the solution from this link docs.microsoft.com/en-us/power-bi/connect-data/desktop-access-database-errors However will try to create on this topic
Sir your video is awesome but please i also need your support: I am new to the DAX and I don’t know the forward step in my scenerio. I have to calculate Days of Sales for last 90 days in groups. i.e. 0-30, 30-60, 60-90, 90-120, >120 days but I am stuck in mentioned below step: '----------> Formula for Days of Sales = Stock Value / Avg. Daily Cost of last 90 days As Stock value for all items were in calculated column so I created a measure which is: Total Net_StockValue:=SUM('QOH_-_All_BUs'[Total Stock Value]) so to calculate last 90 days cost I have to first calculate mention below: which I did: Total Cost:=SUM(Sales[Cost]) Day Before 90 Days:=[Today]-90 # of Days = 90 Then I calculated Cost for last 90 days: 90 Days Cost:=CALCULATE([Total Cost],DATESBETWEEN('Calendar'[Actual Date],[Day Before 90 Days],[Today])) then I calculated Average Daily cost for last 90 days: Avg. Daily Cost for Last 90 Days:=DIVIDE([90 Days Cost],[# of Days]) then as per formula I have calculated Days of Sales: Days of Sales:=DIVIDE([Total Net_StockValue],[Avg. Daily Cost for Last 90 Days]) Now I have to calculate Days of Sales for last 90 days in groups. i.e. 0-30, 30-60, 60-90, 90-120, >120 days. But I don’t know how to do it and my employer asking me again and again….Any help will be highly appreciated: Please need any one support...…..Thanks
I am trying to understand this. In one statement you said days of sales for only 90 days then what is the purpose of showing them in groups. Once you have fixed 90 days values it is not possible to segregated by other groups. Some thing is missing in the explanation. I have done all formulas above but still not understanding the objective. Please rephrase the question so that I can definitely help you.
Vijay, your video was excellent. Helped me solve one of my DAX issues.
You are always welcome. If you could describe more so that it may help other subscribers as well
@@vijay_ I wanted to count employee by age groups. My model has FACT rows populated by month for EoM metrics. One very silly mistake I was doing is that I calculated age in my source excel and assumed it was numeric. DAX showed error and I struggled for some time before I realized that the field should be defined numeric or whole number. The second challenge was to ensure that for the selected FY filter, I needed the latest set of metrics and here is where I used a Variable to get MAX action date and included the same in my filter.
Pretty satisfied with my DAX measure now as it can help create multiple analysis around these age groups.
I guess it is kinda off topic but does anyone know a good place to watch new series online ?
@Roger Ronan Flixportal =)
@Gregory Christopher Thank you, signed up and it seems to work :D I really appreciate it!!
Simple and very effective measures 🙏🙏🙏🙏🙌👏
Explained it in very detail level and easy to understand.. Only few people knows teaching well and u r one of them sir.. Great
Thanks for Feedback
@@vijay_ sir, is it possible to upload video on how to send bulk auto emails to customers with their statements based on their ageing.
@@krishnanseshamanikrishnan1950 not really required to send bulk email.
Once you publish the report to power bi service and share with team members.
They can use this report , but will th pro license.
And subscribe all the use s to the report thru will get an email with ageing report on the e-mail body without any bulk mail mail setup
Learnt heaps, but for me one of the most powerful pieces of information was when you referred to the number of closing parenthesis and how you know you have included the right amount was when the tool tips box disappeared (@8:25) - never realized that this was a great visual tip for having the correct number of closing parenthesis - so simple!
Very clear explanation 👌 easy to understand and learn...Thanks
Awesome tutorial, I loved your teaching style here. Very easy to follow for someone familiar with Excel but completely new to PowerBI.
Thanks for the valuable Feedback
Grate Job! Thanks.
Very nice video and very well explained! Kudos!
Awesome video ji its really great
You have explained it in a very simple way. It was really helpful.
Glad to hear that
Thank you for sharing this, this has help a great deal. Excellent video and well presented.
Glad it was helpful!
Very good video.
Thank you very much!
Vijay excellent video...really appreciate your hard work on this video...i am new to Power BI and your ageing report was highly creative.
Hi Sukhvinder Thanks!! If you wanted to learn Power BI please reach out , I can help you!
This tutorial was very helpful. Thank you
Thanks for Feedback
Great job on this. So much valuable information in a short amount of time! Easy to follow and understand. Thank you.
You're very welcome!
Very well explained..! 👍😊👏
Glad you liked it
Superb Video Very helpful. Thx for sharing the same.
Glad it was helpful!
Very easy tutorial sir, thank you so much
You are most welcome
Awesome awesome awesome... Highly thankful to u 🤗
Most welcome 😊
Great work you did. I love your work. Thank you
Thank you very much!
Vijay- Video was nice. Just query in Ageing calculation the "&" formula is not working. So do we need to activate anything
It is twice &&
Thank You, this helped me out. However, this causes performance issues in the report. Any alternative solutions to get the same outcome?
You can create a measure for Age in days thru measure instead of Column to make bit more.efficient
Great sir.. Really loved this video.. Keep motivation us..
The formula is not working with me and I don't know why, I have tried to track your steps with no success
Inv 120-360 = CALCULATE([Total Invoices],FILTER(ALL('الجدول1'[AGE],'الجدول1'[AGE]>=120&&'الجدول1'[AGE]
After filter ,distinct(invoice no))
this is exactly what i was looking for but the formula at 8:29 is confusing. Would appreciate it if it was broken down more.
Great video. I don't get the functionality of Countrows in outstanding measure... what part countrows actually plays to the sum of each age group?
It evaluates
Each individual age rows to make sure the number rows more than 0 .
Based on that the data will be populated on the context filter with other age group table.
Great sir
Thanks
Hello Vijay. Great video as it helped me a lot. One question I have is, why is the age grouping done as 0-30, 30-60, 60-90 instead of 0-30, 31-60, 61-90 and so on? Thanks in advance.
Because if you look at the formula I hv considered that.
Agen in days > Menasha we are exclude 0,30,60 etc and the values will be produed accordingly
You can change the to 0-30 , 31-60 and so on in the table what you create
@@vijay_ Thank you... I found that I needed to updated the Outstanding formula to read >=Min and
Thanks for the great tutorial. Can you please also give some more insights for historical aging accounts receivable amounts for the selected date in the past? Say, I want to compare last months aging AR to current month or week. How to built in the paid amount or paid date into formula?
You can create Datetable and use dateadd function to see what is previous period status of same invoice or sake customer.
@@vijay_ Thanks Vijay. Can you create a video on your channel and show that based on your database? I found there were a lot of people asking same question as mine, but there are no tutorials on aging AR historical trends.
How to go to the 2nd line while calculating new measure. If I click enter it didn’t go the néw line 4:35
Shift+Enter to go to second line
How do you update this report once some of the invoices are paid?
In the source data you can create two columns one payment received and another one is balance Ostanding.
Use balance Ostanding column for your calculations to hv control on the as on date value.
Hello Vijay Sir. I just came across this vdo. Are there more vdos on AR related please? I am looking for Weighted average days by cust calculation please.
This was very helpful. Thank you. I'm trying to figure out how to add retainage to the groups and basic measures. In my table I have a field for amount due and a field for retainage due. How can I get the retainage due to show in my visuals? I need the total retainage due not based on aging.
If you can share a.sample.dummy data , i can explain better with another video
Vijay.perepa@ameetz.com
Vijay, Good Video. Is there a way to link your source table (Debtors) to your group table (Groups)? I am trying to cross filter a table consisting with all of my source data with my aging groups and there are no relationships established between the two tables. I tried creating a column for today's date however this Many/Many relationship is not allowing the results to cross filter. Thank you,
Hi Alec, Let me look into and create a model so that I can explain you. Give me little time. Mean time watch my latest video on M query and let me know your views Thanks
@@vijay_ Vijay, Any update on this? Thanks
Hi Vijay, your video is very helpful. I am also looking for a solution how to link Groups table to the fact table. Thanks in advance
Good video ... Simple explanation . Two questions with practical perspective1 This is an as on date report what about if we want to have ageing as on specific date . 2 what about payment against your invoice , how would you handle this if the data comes from erp directly. considering as on date would be simpler but again how do you manage as on specific date . There will be application against each invoice ?
If the Data comes from the server, you can take the data (only Outstanding) to make the logic used in the Video possible. We need to run an SQL Query.
Yes we can create on a specific Date context also using DAX.
hello, what if i want to use the first day of the current month - invoice date, how do to this?
Then add a new column containing a current month day 1
With
= 1 & FORMAT(TODAY (),,"MMM")&YEAR(TODAY ()))
And convert the data type to date
Age in days = DATEDIFF( invoice date,new column , Day), will give you the required result
Could you pls create a inventory aging Report for given period of time with slots(0-90,91-120,121-181...) With inventory and Value of these buckets/slots
Hi Vijay, great tutorial... Can you share the Excel workbook so that we can follow along and plot the visuals. TIA.
find this
drive.google.com/open?id=1SYjDTCsUR4pQx8mQf5auOVS2d2F3Htec
I suggest to use your own data
Many thanks for the video. Its been very useful. I have a question on one of the measures ie the Invoice 120-360 days. where do I insert a ZERO number in the formula in order to ensure that I don't get a (Blank). I inserted a ZERO at the end of the measure and got an error
End of the formula just add +0
@@vijay_ Fantastic. It works now ( I wrongly added a comma before the zero instead of using the plus sign. Many thanks for sorting this.
You are welcome
Hi Sir,
I want to learn Power Bi can u suggest how to learn. One on one have been given a project by my company. Trying online videos but its not helping me too much.
It is always better to get trained by a professional trainer.
You can visit few best institutions in you area to find a trainer. You tube videos are basically one time reference points
how if the age less than 0 Days?
and needed to input Current
Great video. Just quick question, do you know why the Age groups are not following the specific order I gave them? In your video they do. But I have 0-30, then 60-90, etc. when I do a pivot/filter table.
You can customise it the way you want
But need to make sure there is no gap. In your case 0-30, and later it was 60-90.,
Between these two all records are not getting evaluated being nin considered in any group, do you must hv consider them in any one of the age group.
Hope that clarifies
@@vijay_ sorry I havent explained well, I have put the same ones than you, without gaps, but when I do a table, they come out in different order :(
I think you are getting order of 0-30 then 60-90, then 30-60,?
Then you need to create an index column in the group table .
Then sort the group column by index column in the columns tools tabs
It's very nice sir..Can you share that Excel so that we also can practice?
You can just create a dummy days with the columns I hv used. It's simple data.
Thank you so much for the video. It is very useful.
I did it for collection by AR Ageing (Invoice date - Received date). When I add filter "Month" in the page, it is ok if we look by month.
But I have a problem when I need to see by YTD, I have created Collection YTD by AR Ageing . The result still same MTD. Do you know what are measures I have missed.
Thanks for feedback.
Please create YTD on ageing value and you need to use the slicer . If still not able achieve please let me know
@@vijay_ Thank you so much for your advice, I'll try.
What if instead of using Today() I want that date to be dynamic based on a slicer?
Selected lvalu(date) in the slicer
Thanks for this video! One question, what is the advantage to doing this data coding in powerbi compared to directly in excel? For example, would this have the same outcome if the groups were already added in the excel sheet?
In Power BI this process much more robust and automated. Ofcourse we can do this in excel as well.
Hello Vijay
This has been a great help in solvin gone of my most complex scenarios. However, I need to have the report as at a specific date in the past based on a filter on calendar date table, hence the age column will be dynamic.
Can you please provide some guidance?
Thanks
Please I have this question,sir: 1..how do I get the outstanding before doing their ageing analysis if I am not using any ERP as your fact sheet was pulled from an ERP (assumed)?
2. If you use an ERP and you pull for the outstanding before doing ageing analysis, may I know how your fact sheet will get updated with new data?
Thank you
Process is same!
I am looking for a power bi developer for a paid task.preferably someone who has experience working with finance data, AR and AP
You can reach me at vijay.perepa@ameetz.com
How if the aging less than 0 Days in groups data?
In practical situation less than 0 shld not be there. However in case of less than 0 make ur ageing group to less 0 also.
Hi Vijay Very good tutorial Possible to share the file? Just came across your video today
hello, sir, I wanna do an analysis of one Fiscal year balance sheet where we have columns of opening , debt, credit, closing. please suggest me a video to do this properly
Please Share some Data to Create a Video
Hi, I need to know how can I define a date do analyse de ageing, it meas what is the to atl amount in debt at a certain date.
Can you rephrase you question. i could not get!
I need to know the ageing in a certain date not only compare with today
Then you need to create the age of invoice based on Inv date (minus) selected date
@@SM-vd1ri Hi, did you find out how to do that? I can't create the age based on the selected date, the calendar table is not active to bring to the formula..
Will be creating soon
Can anyone help to breakdown the Outstanding measure formula?
How to you can get the this data pls tell me
I got a error in power bi to load access database as container existed unexpectedly error code ..............do you have any way to deal this
Power bi cannot handle data from xls and xlsb. Use only xlsx data
@@vijay_ sir am talking about Microsoft access database
@@shubhamsharma8373 Try the solution from this link docs.microsoft.com/en-us/power-bi/connect-data/desktop-access-database-errors
However will try to create on this topic
Sir your video is awesome but please i also need your support:
I am new to the DAX and I don’t know the forward step in my scenerio. I have to calculate Days of Sales for last 90 days in groups. i.e. 0-30, 30-60, 60-90, 90-120, >120 days but I am stuck in mentioned below step:
'----------> Formula for Days of Sales = Stock Value / Avg. Daily Cost of last 90 days
As Stock value for all items were in calculated column so I created a measure which is:
Total Net_StockValue:=SUM('QOH_-_All_BUs'[Total Stock Value])
so to calculate last 90 days cost I have to first calculate mention below: which I did:
Total Cost:=SUM(Sales[Cost])
Day Before 90 Days:=[Today]-90
# of Days = 90
Then I calculated Cost for last 90 days:
90 Days Cost:=CALCULATE([Total Cost],DATESBETWEEN('Calendar'[Actual Date],[Day Before 90 Days],[Today]))
then I calculated Average Daily cost for last 90 days:
Avg. Daily Cost for Last 90 Days:=DIVIDE([90 Days Cost],[# of Days])
then as per formula I have calculated Days of Sales:
Days of Sales:=DIVIDE([Total Net_StockValue],[Avg. Daily Cost for Last 90 Days])
Now I have to calculate Days of Sales for last 90 days in groups. i.e. 0-30, 30-60, 60-90, 90-120, >120 days. But I don’t know how to do it and my employer asking me again and again….Any help will be highly appreciated:
Please need any one support...…..Thanks
Will come back to you!
@@vijay_ Sir can i have a solution to my question please?
I am trying to understand this. In one statement you said days of sales for only 90 days then what is the purpose of showing them in groups. Once you have fixed 90 days values it is not possible to segregated by other groups. Some thing is missing in the explanation. I have done all formulas above but still not understanding the objective. Please rephrase the question so that I can definitely help you.
I find difficult to understand the logic 😕 what is happening behind the scene.
Segmentation
Nice video. Please share .pbix file
Did you get the pbix?