Відео

I love the music…❤❤❤❤

Extraordinarily concise. many thanks! using Excel 365 insider: =LET( unpiv, LAMBDA(arr, LET( _col, COLUMNS(arr)-1, _fil, ROWS(arr)-1, _reg, SEQUENCE(_fil*_col), arrFils, INDEX(DROP(CHOOSECOLS(arr,1),1),ROUNDUP(_reg/_col,0)), arrCols, INDEX(DROP(CHOOSEROWS(arr,1),,1),MOD(_reg-1,_col)+1), arrDatos, TOCOL(DROP(DROP(arr,1),,1)), arrkey, HSTACK(SEQUENCE(ROWS(CHOOSECOLS(arr,1))),CHOOSECOLS(arr,1)), arrMesesKey, HSTACK(SEQUENCE(12),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}), res, HSTACK(arrFils,arrCols,arrDatos), x, HSTACK(XLOOKUP(CHOOSECOLS(res,1),CHOOSECOLS(arrkey,2),CHOOSECOLS(arrkey,1)), CHOOSECOLS(res,1), XLOOKUP(CHOOSECOLS(res,2),CHOOSECOLS(arrMesesKey,2), CHOOSECOLS(arrMesesKey,1)), CHOOSECOLS(res,2,3) ), x) ), mth, 5, y_a, unpiv('2021'!A3:M16), y_b, unpiv('2020'!A3:M16), grp_a, GROUPBY(CHOOSECOLS(y_a,1,2),CHOOSECOLS(y_a,5),SUM,0,0,,CHOOSECOLS(y_a,3)<=mth), grp_b, GROUPBY(CHOOSECOLS(y_b,1,2),CHOOSECOLS(y_b,5),SUM,0,0,,CHOOSECOLS(y_b,3)<=mth), HSTACK(CHOOSECOLS(grp_a,3), CHOOSECOLS(grp_b,3)) ) unpivot table: 2 Revenue 1 Jan 20212 2 Revenue 2 Feb 22737 2 Revenue 3 Mar 23903 2 Revenue 4 Apr 23541 2 Revenue 5 May 23971 2 Revenue 6 Jun 20321 2 Revenue 7 Jul 22467 2 Revenue 8 Aug 20566 2 Revenue 9 Sep 22952 2 Revenue 10 Oct 25017 2 Revenue 11 Nov 21902 2 Revenue 12 Dec 23724 3 Cost of Sales 1 Jan -11354 3 Cost of Sales 2 Feb -13076 3 Cost of Sales 3 Mar -12976 3 Cost of Sales 4 Apr -13906 3 Cost of Sales 5 May -14503 3 Cost of Sales 6 Jun -11676 3 Cost of Sales 7 Jul -11676 3 Cost of Sales 8 Aug -12276 3 Cost of Sales 9 Sep -12976 3 Cost of Sales 10 Oct -14576 3 Cost of Sales 11 Nov -12676 3 Cost of Sales 12 Dec -12876 4 Gross Profit 1 Jan 8858 4 Gross Profit 2 Feb 9661 4 Gross Profit 3 Mar 10927 4 Gross Profit 4 Apr 9635 4 Gross Profit 5 May 9468 4 Gross Profit 6 Jun 8645 4 Gross Profit 7 Jul 10791 4 Gross Profit 8 Aug 8290 4 Gross Profit 9 Sep 9976 4 Gross Profit 10 Oct 10441 4 Gross Profit 11 Nov 9226 4 Gross Profit 12 Dec 10848 5 Salaries 1 Jan -3132,86 5 Salaries 2 Feb -3524,235 5 Salaries 3 Mar -3704,965 5 Salaries 4 Apr -3648,855 5 Salaries 5 May -3715,505 5 Salaries 6 Jun -3149,755 5 Salaries 7 Jul -3482,385 5 Salaries 8 Aug -3187,73 5 Salaries 9 Sep -3557,56 5 Salaries 10 Oct -3877,635 5 Salaries 11 Nov -3394,81 5 Salaries 12 Dec -3677,22 6 Marketing 1 Jan -1414,84 6 Marketing 2 Feb -1591,59 Thanks ChatGPT: The formula is designed to unpivot data from a table format and then group and summarize it based on specific criteria. The final result is a Year-to-Date (YTD) comparison of the aggregated data for two different years (2020 and 2021) up to a specified month. Here's a breakdown of the key components: 1. Unpivot Function (unpiv) Parameters: arr is the input array (a table of data). Purpose: The unpiv function transforms a table where each row represents a different line item (e.g., Revenue, Cost of Sales) and each column represents a different month, into a format where each row represents a specific line item for a specific month. Steps: Calculate the number of columns (_col) and rows (_fil) in the input array, excluding the header row and column. Generate a sequence (_reg) for indexing purposes. Extract row identifiers (arrFils) for each line item, repeating the identifier for each month. Extract column identifiers (arrCols) for each month. Unpivot the data (arrDatos) into a single column of values. Create key mappings (arrkey, arrMesesKey) for line items and months. Combine the unpivoted data (res) with the relevant keys. Final output (x) is a table with line item indices, descriptions, month indices, and unpivoted data values. 2. Monthly Limit (mth) Purpose: Defines the cutoff month for which data should be considered in the grouping and summarization. This limit is used to calculate YTD figures. 3. Apply unpiv to the Years 2020 and 2021 y_a: Unpivoted data for the year 2021. y_b: Unpivoted data for the year 2020. 4. Group and Summarize (GROUPBY) Parameters: The data is grouped by the line item (e.g., Revenue, Cost of Sales) and summarized by summing the values for each line item up to the specified month (mth), which represents the YTD figures. grp_a: Grouped and summarized YTD data for 2021. grp_b: Grouped and summarized YTD data for 2020. 5. Final Output HSTACK(CHOOSECOLS(grp_a,3), CHOOSECOLS(grp_b,3)): The final result is a side-by-side comparison of the YTD aggregated values for 2021 and 2020, up to the specified month, for each line item. Summary This formula creates a Year-to-Date (YTD) model that takes financial data from 2020 and 2021, converts it into a row-based format, and then compares the YTD results for the first few months (up to mth) of each year. This YTD model allows for an easy comparison of key financial metrics such as Revenue, Cost of Sales, and EBITDA between the two years. Final result: Revenue 114.364 111364 Cost of Sales (65.815) -64915 Gross Profit 48.549 46449 Salaries (17.726) -17261,42 Marketing (8.005) -6681,84 G&A (7.777) -7572,752 Others (3.431) -3340,92 EBITDA 11.609 11592,068 Depreciation (2.750) -2640 Interest (3.060) -3005 EBT 5.799 5947,068 Tax (1.160) -1189,4136 Net Income 4.640 4757,6544

Very useful tutorial, thank you very much

Definitely a mind bender. -Thanks!

Thanks a lot you saved like hours for me!

Very informative. Please make more such videos on subscription based metrics.

Many Thanks!!!

Thank you for being simplified with your tutorials. It's my first time on your channel but stocked with it already. Thank you so much. I would appreciate you also create more tutorials on life scenarios or real business questions for those looking to go into Data analytics. Thank you so much

thank you so much

Hello. Can we get the excel file?

You explained it very good. Thanks so much.

Nice one mam

Thank you. Very helpful. I would like to know how will you apply variable exchange rates with currency switch across different time series or time points?

Hello Bunmi. Thank you. It is possible provided both parameters are null, Power Query returns 0 rows?

Explain three types of cell refrences

very nice job, but first date purchase use G2 as false value in formula, which is lead to copy of head column for value> 1

wonderfull

Great

Cell reference explain

Many thanks for sharing your knowledge, it’s very useful 😊

Easy to understand

Hi 🎉. Your presentation is fantastic 😘.

your videos are excellent can you cover more topics on dax please

Awesome explanation! Thank you

With this formulas we can create so many programs in Excel

Thank you very much ❤❤❤❤

I was trying to recreate a waterfall chart for work and there way was so complex. You video made it so easy I just restarted the whole thing and it was done in minutes.

can you show me how to do YTD growth% on Rev

Another great video, cohort reports well explained, thank you

Thank you for making this video

Hi. why age by month devide by 30? 30 is what ya? sorry 😢

I used pivot table and index match to find first purchase(it was easier to me) but the cohort table part is invaluable thank you very much!!

what if the lookvalue isnt a number?

P R O M O S M 👀

Thank you for this video. it was helpful. May i ask if you have a version for mac users because some of the steps are different. What is the manual instruction for f4.

I used to mix them up. Thanks for making it very clear

Very good job! Appreciate you

Nice trick with an IF statement =) I just implemented it in my Excel issue!

Thank you!!

Please how were you able to remove the month in the front of the total value when summed up with "ALT+=" because mine is showing the month in front of it and i don't have an idea how to remove it

The countif variant of this formulae is sheer simplicity! Prefer that.

What an explanation, super mam

Wonderfully explained! Thank you so much!

Thank you so much for this video. This is perfect for the presentation I am giving for my colleagues next week. I want to teach them the magic of parameters and this is better than I could have hoped for. Perfect way to show how they work. Thank you very much.

Well explained. PLEASE give the files

useful knowledge. please put excel files in the description ----, where is it?

Why did you not add in the active in retention rate table?

Thanks sir

The total all rows , it does not seems Right , like late column. Is 127 , but on your work , is was 134 ? Can you check for it ?

Welldone? Why did you turn off, structured referencing, I thought is a more better way of writing a formula in a table.