Thank you sir, my all concepts are getting cleared by your precise explainations. I appreciate your efforts from bottom of my heart. I shared you channel to my friends. Again thank you. one request - pls upload more videos on more dax functions.
1) To create dynamic Last year sales measure which can dynamically changed to month/quarter by drilling down: Sales PrevPeriod = Calculate(Sum(Sales[Sales Amount]), SamePeriodLastYear(DimDate[date].[Date]))
Hi Nags, I am not getting Dimdate option by default when I am trying to write Sameperiodlastyear formula? Do you know why I am facing this issue? Thank you
The concept was very well explained. However, I am facing some issue in this usecase. Would like to calculate growth over corresponding period of the previous year. The granularity needed is calendar month. Model comprises of EmployeeData [FACT] and Calendar [DIMENSION] linked on EmployeeData.ActionDate and Calendar.Date columns. EmployeeData has one row for every active calendar month wherein ActionDate is the last day of month and RecType = “Closing”. For every new joining employee a row is added in the joining month with ActionDate = Date of joining and RecType = “Joiner”. Next we have for every exiting employee a row in the month of exit a row with ActionDate = Last working date and RecType = “Leaver”. We have “FinYear” as slicer wherein our FinYear is Apr-Mar. Current selection by default is “2021-22” and we are in the month of June i.e. 3 months into the Fin. Year. Explanation: 1. Selected FinYear is 2021-22 2. We have 4 (Apr) + 9(May) joiners in the current year (2021-22). No joiners in the month of June. 3. Joiners count displayed is 13 and that is also same as YTD Joiners. 4. There were 39 joiners in the previous year with just 1 during Apr-Jun period. 5. We are using the KPI visual for Joiners and expect to display 13 as the Indicator and compare it with a Goal 1 (Apr-Jun of FY 2020-21) to growth displayed as 130%. 6. We are using SAMEPERIODLASTYEAR function with Joiner and it considers 39 instead of 1. Advance thanks for your guidance.
All these date calculation works closely with date column. Either you need to choose this quarter as a slicer or filter. Since you don't apply any slicer or filter for the quarter..you chooses entire financial year in a slicer..it consider whole previous year.
@@AnalyticswithNags Does that mean that date period is full Fin Year (Apr-21 to Mar-22) and hence SAMEPERIODLASTYEAR considers Apr-20 to Mar-21 to evaluate the value. What do you recommend if I wish to look at number of months in current selection and limit SAMEPERIODLASTYEAR functionn also to the corresponding months of LY.
@@AnalyticswithNags Thanks yes this seems to do the trick. Now the next requirement is how to dynamically set this visual level filter with a measure. I am trying but without much success. Can you please share some references that can help achieve this. Advance thanks.
When I drill down my data by client or product I get the below error (this happen where there are months for a particular client/product with no volumes): “Function ‘SAMEPERIODLASTYEAR’ expects a contiguous selection”. Do you know how to solve this problem? Thanks
Yes, usually to handle such a scenerio, Insert a record for client and product combination for missing dates with volume as 0 in the database. This how we usually handle such a scenerio
@@AnalyticswithNags maybe is fine if you have a small table that is easy to amend manually. Definitely not ideal if you have a lot of data. Hopefully Microsoft will fix this issue soon...
Hi Ravi, we are extremely happy to see a aggressive learner like this. We are busy this week, as we are also working professionals and committed to our dealines. We will upload videos regularly from next week. Keep learning.
Much appreciation to you. You are actually changing lives for better. Thank you.
Thanks keep learning share this channel with friends and colleagues
Thank you sir, my all concepts are getting cleared by your precise explainations. I appreciate your efforts from bottom of my heart. I shared you channel to my friends. Again thank you. one request - pls upload more videos on more dax functions.
Thanks keep learning share this channel with friends and colleagues. Sure will upload more videos
Big thank you for that filter tip! That solved a lot of the issues I been running into.
Thanks keep learning
@@AnalyticswithNags sir can u share the data set plz
salute to this guy. :)
Thanks keep learning share this channel with friends and colleagues
Superb..very nicely explained dear...best wishes
Thank you so much 🙂
Amazing
Excellent Explanation skills. Kindly Provide the schema of Dimdate,ProductMaster,SalesMaster tables
Check the description of the video.
great sir,way of explaining every point ,even tiny point u covered.
Thanks a ton
Thanks for zoom out your code ❤
No problem 😊
1) To create dynamic Last year sales measure which can dynamically changed to month/quarter by drilling down:
Sales PrevPeriod = Calculate(Sum(Sales[Sales Amount]), SamePeriodLastYear(DimDate[date].[Date]))
Thankyou so much .
You're most welcome, Keep learning
excellent description naga's !! please keep it going
Thank you so much 🙂
Why we are using .date ? Will you provide the video link?
Check the channel with same question
I think same period last year is used in calculate function intern acting as a filter internally
That’s true
How did you find the DIMDATE,SALES table ? There is no such table in the data set.
can you pls tell us how you have created that hierarchy Date table?
Check the date table creation video
Hi bro, For me drill down button is not enabled in the visual. how to enable ?
You need to add one more level in the column.
Hi Nags, I am not getting Dimdate option by default when I am trying to write Sameperiodlastyear formula? Do you know why I am facing this issue? Thank you
Dimdate is a calculated calendar table. You need to create a table
Can I get previous financial year till date(PFYTD) with this function please
You need to add PYTD(2022dec31) value with CYTD
Why the formulas are not in the same line ? Is it OK if I write formulas in a single line
sir please provide the dim date Table & Sales Table, i have already checked in description tables is not available
ruclips.net/video/BnqtBxGbo0M/видео.html
Follow the complete playlist for sales fact
what about dates in different fiscal year? This calculation gives me year closing December..I want to have Apr-March FY..
Create is fiscal year flag and filter that in the report
Sir why we use .date
I have been waiting for that
Check with same search in channel, I have made a separate video on this
The concept was very well explained. However, I am facing some issue in this usecase.
Would like to calculate growth over corresponding period of the previous year. The granularity needed is calendar month.
Model comprises of EmployeeData [FACT] and Calendar [DIMENSION] linked on EmployeeData.ActionDate and Calendar.Date columns.
EmployeeData has one row for every active calendar month wherein ActionDate is the last day of month and RecType = “Closing”.
For every new joining employee a row is added in the joining month with ActionDate = Date of joining and RecType = “Joiner”.
Next we have for every exiting employee a row in the month of exit a row with ActionDate = Last working date and RecType = “Leaver”.
We have “FinYear” as slicer wherein our FinYear is Apr-Mar. Current selection by default is “2021-22” and we are in the month of June i.e. 3 months into the Fin. Year.
Explanation:
1. Selected FinYear is 2021-22
2. We have 4 (Apr) + 9(May) joiners in the current year (2021-22). No joiners in the month of June.
3. Joiners count displayed is 13 and that is also same as YTD Joiners.
4. There were 39 joiners in the previous year with just 1 during Apr-Jun period.
5. We are using the KPI visual for Joiners and expect to display 13 as the Indicator and compare it with a Goal 1 (Apr-Jun of FY 2020-21) to growth displayed as 130%.
6. We are using SAMEPERIODLASTYEAR function with Joiner and it considers 39 instead of 1.
Advance thanks for your guidance.
All these date calculation works closely with date column. Either you need to choose this quarter as a slicer or filter. Since you don't apply any slicer or filter for the quarter..you chooses entire financial year in a slicer..it consider whole previous year.
@@AnalyticswithNags Does that mean that date period is full Fin Year (Apr-21 to Mar-22) and hence SAMEPERIODLASTYEAR considers Apr-20 to Mar-21 to evaluate the value.
What do you recommend if I wish to look at number of months in current selection and limit SAMEPERIODLASTYEAR functionn also to the corresponding months of LY.
You need to filter the period in slicer. Then current year measure and LY measure will give you the expected results.
@@AnalyticswithNags Thanks yes this seems to do the trick. Now the next requirement is how to dynamically set this visual level filter with a measure. I am trying but without much success. Can you please share some references that can help achieve this. Advance thanks.
I continued with my efforts to resolve it by myself and have fixed it. Thought I should keep you informed and not waste your time. Thanks once again.
Can we take total sales instead of sales amount
Yes
Hi Bro in the description the file you have given in that file Sales table and DimDate table not there. Please give that file also
Dim date check the video related to dim date table creation
Hi nags please upload remaining 13 videos also
All videos are ongoing. Subscribe to get the latest updates from the channel
Hi sir while showing previous year tooltip is showing current year only...like cursor displaying same year
Not sure why it is happening for you.
@@AnalyticswithNags sir in video it is showing....
hai bro your explantion is good ..in this video i have some problem how to measure date function?
Let me check, email me the details
@@AnalyticswithNags rajeevpatchigolla@gmail.com
@@rajeevpatchigolla6713 email to my email id , the details of your issue , check the desc for my email id
sir where is sales table ...from the data source link there is data nd product master but not sales table can u pls reply
Check the description of the video.
Hi sir , can you plse help me to how to create dim tabel using sales tabel or else can you plse upload dimdate datasource link
Check playlists of this channel and explore topics on the similar line requested by you.
Sales tables is not available in the Excel file...
Check previous video description
Hello all, could anyone let me know regarding row wise sales for sum(sales amount) in table chart. I am not getting that., help needed.
sir , how to create date hierarchy
Check in channel searching how to create date table
I am trying the same function but instead of previous year value of current year is only coming can anyone help
Not sure
I am getting error as samplperiodlastyear require contiguous period
last year January 01 week for 3rd jan was there and this year it is 52 for 3rd jan
Not sure why this is happening to you
How to get current year sales only? Please explain I have been used TOTALYTD function but it showing same sales by years regarding.
Watch this two videos
ruclips.net/video/uDQshJTP56s/видео.html
ruclips.net/video/tXdze9PxYIg/видео.html
Choose the one that suits your requirement
hi sir,drill down feature is not showing in chart
Only if you have two columns or hierarchy then only drill down will appear
How's the dim date came?
check dim date creation video
If we do the sales per day with leap year what will it show for the 29 feb
yes
When I drill down my data by client or product I get the below error (this happen where there are months for a particular client/product with no volumes):
“Function ‘SAMEPERIODLASTYEAR’ expects a contiguous selection”.
Do you know how to solve this problem?
Thanks
Yes, usually to handle such a scenerio, Insert a record for client and product combination for missing dates with volume as 0 in the database. This how we usually handle such a scenerio
@@AnalyticswithNags maybe is fine if you have a small table that is easy to amend manually. Definitely not ideal if you have a lot of data.
Hopefully Microsoft will fix this issue soon...
where is the dataset?
we dont maintain excel , prepare your own file like the one shown , with your own sample data.
How to reach you sir
Email to analyticswithnags@gmail.com
Why is this video not getting played
check from other device
please update new video sir
Hi Ravi, we are extremely happy to see a aggressive learner like this. We are busy this week, as we are also working professionals and committed to our dealines. We will upload videos regularly from next week. Keep learning.
@@AnalyticswithNags thanks sir,because i
want learn and grow up in i.t filed .Now i am fresher but have a good experience in power bi because of you.