Two Date Columns in Fact Table? How To Create Report? DAX, Power Query, Excel? 365 MECS Class 22
Вставка
- Опубліковано 3 лип 2024
- Download Zipped Folder with All Files: excelisfun.net/files/22-M365E...
Download pdf notes: excelisfun.net/files/22-M365E...
This video teaches how to create reports when you have two date columns in a Fact Table Three ways: Worksheet Formulas, DAX Data Model Formulas and Power Query. Learn about how to create a Side By Side Report and a Cross Tabulated report for ordered sales and shipped sales. Learn about USERELATIONSHIP DAX Function, and how to create two date dimension tables using Power Query in the Data Model. See solutions in both Power BI Desktop and Excel Power Pivot.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: • Microsoft 365 Excel & ...
This video covers.
1. (00:00) Introduction and video topics
2. (00:33) Fundamental problem when you have two date columns in Fact Table and Required Reports for a Fact Table with Order and Ship Dates.
3. (01:58) Worksheet Formula Solution using SUMIFS Function for both types of reports: side-by-side ordered and shipped orders reports and cross tabulated reports
4. (09:46) Data Model Solution with the USERELATIONAHIP DAX function to create a side-by-side report.
5. (13:42) Power Query to create two data tables for a cross tabulation report that shows ordered sales and shipped sales broken apart by ordered and shipped years.
6. (17:39) Video Summary and Conclusions
7. (17:59) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/... . Artist: audionautix.com/
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #powerquery #Dax #powerquery #datamodel #datamodeling #datascience #datetable #dimensions #dimensionformula #order #ship #shipping
I will start to learn from the first video of this series today :) hope I can reach this last video soon
Awesome video Mike 📹 👏 Thank you for shwoing all types of solutions
You are welcome, Nader!!!!
Another epic video with usual business topics we deal with
Glad you like it!!!
The best Teacher so far by years light from others!. Thanks Mike for this amazing Master Classes MECS Journey. Really enjoyed all chapters.
You are welcome for the fun class!!! I might be a good teacher, but a REALLY bad marketer with only 3000 views in 3 days lol
Thanks Mike, fantastic video as always! MECS was epic!
You are welcome as always, Chris M!!!! MECS was fun : )
Another great video from one of my very favourite channels. Keep up the good work!
Glad you like this, Roy!!! Thanks for your support : )
Great video as usual!!✌
Always having fun replacing ...IFS functions 😉 => formula at H14
=MAP(YEAR(IF(H13#,G14#)),YEAR(IF(G14#,H13#)),LAMBDA(x,y,SUM((x=YEAR(fSales[OrderDate]))*(y=YEAR(fSales[ShipDate]))*fSales[Sales])))
Forever avoiding SUMIFS : ) : ) Thanks for the keen insight and cool MAP Year arrays : ) : ) I added your Exceedingly clever solution to the Finished Download Excel File. Go Team!!!!!
Thank you so much sir .
Thanks Mike. "Userelationship" that was awesome!!! I never used it!!
You are welcome, Formula Guy!!!!!
Thanks Mike! For another great series. Keep up the great work. Always reminding me of the awesome things Excel can do. The only app that matters!! Excellent books to by the way.
Glad you like the book : ) Only App That Matters : )
That's EXCELlent ... Thanks, amazing Mike.
You are welcome, Fellow Teacher!!!!
This was a good video to understand the relationship between two date columns and the most unique feature in this video was "userelationship" feature.
I have a very interesting request and if possible please make a video on "Stock profile " dashboard using stock function
Glad this video is helpful!!!!
Fantastic
Cheers from Brazil.
Glad you like it!!!!
Congratulation! The complete story is now "complete"!?! Things that start seemingly must end. BUT grandpa had a garden. He recruited my reluctant grandma to help him plant. One day I heard her say "Thank God thet planting is done. Time to celebrate and have some fun. There is no time for that said grandpa. Got a lot of weedin', hoein ' and pickin' to do! I tend to view Excel though my grandpas eyes. Excel has a lot of weedin', hoein' to do and perhaps some some plantin' and pickin' too! So lookin' forward to MERCS Micosoft Excel the REST of the complete story!
lol * 100. MERCS Micosoft Excel the REST : ) You are 100% right, Excel and Power BI are infinite. But I am pretty sure that this is THE most complete video class ever created that covers the foundations of: Excel, Worksheet Formulas, Standard PivotTables, Power Query, M Code, Power Pivot, Power BI and DAX. But you are sooooooo right, this foundation is just the jumping off point towards efficient Excel and Power BI Infinity. To Infinity and Beyond!!!! Go Team : )
Thank you mike for your efforts... I have learnt a lot from you... Great Video..
I have one query not related to this.. .. Is there any way we can do month on month Running Count for a financial year.. And when the new financial year starts it should start from scratch..
You are welcome! There is always a way. Not sure what your details are, but THE best place to ask questions and have back and forth dialog to get solutions is: mrexcel.com/board (Mr Excel Message Board).
hi Mike,
could you please make video on heirarchical Bill of material???
minute 01:55 = Worksheet formulas = we can do whatever want! = I prefer this 😁ahaha
minute 3:55 = felt like ninja kung fu move.....😵😵
It is true!!!! Radly so : )
Fantasticcc as always, 18 minutes well spent!! thank you Mike!!
Minute - will there be a video 23???
@@spilledgraphics You are welcome, Brother of Rad!!!!
Hey Mike,
approximately how many videos will this series contain?
I guess you are not subscribed and have not seem my home page. Check out home page and watch intro video: it has all the good details about the classes and how to find them, including this one, second from the top, with 22 videos : ) Home page: www.youtube.com/@excelisfun Intro video: ua-cam.com/video/GNhN1Zw8oM0/v-deo.html full class: ua-cam.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
Hey Mike: thanks. I noticed in your Power BI file that the relation between SALES and SHIPDATE is 1:1. This is because all the shipdates are unique as are (of course) the calanderdays in SHIPDATE. So I wanted to figure out what happened if the Excel file changed and I duplicated a shipdate. Well: Updating in Power BI (Refresh in Home) gave me an error. Actually as expected but the formula message was (as usual) not clear. So I tried updating Power Query: No errors, and the values updated. So far so good. But then Load to Power BI also worked, but the new data was NOT updated. No warnings or what soever. So bottom line: Make sure your relations are always correct. With great pleasure I think of the times of ACCESS where enforce "referential integrity" was so helpfull.... Unfortunately this is not in Power BI. Was this the last MECC? thanks for all these video's.
Thank you Mike i have a question when i create a pivot table with top 10 valu and filter it to show value as percentage % the result calculated pased on that 10 value not the all record in the original table that have 200 record
I don't understand. But no worries, the best place to ask Excel questions and get back and forth dialog is: mrexcel.com/board
❤❤❤❤❤❤❤❤❤❤❤❤❤
Glad you like it! Thanks for the love : )
@@excelisfun شكرا على إبداعك
Thanks for your creativity
I am getting month sorted by Alphabetical order and if there is mix of year then it
appears like APR-23, APR-24, Aug-23, Aug-24 - How to fix it ?
Dear Sir if price of product change in fact table. How will we deal it? Please make a dedicated video on this. If price change?
You have to use two columns as a key and make a lookup table, like with date and product as the key for price.
@@excelisfun ❤️❤️❤️❤️❤️❤️👍👍
1st comment
You get the first place trophy!!!!
Amazing work as always!
This time around, I've tried my hand at creating a single-cell formula. Here's what I've come up with:
=LET(
s, fSales[Sales],
oy, YEAR(fSales[OrderDate]),
sy, YEAR(fSales[ShipDate]),
cd, VSTACK(oy, sy),
ud, UNIQUE(cd),
sd, SORT(ud),
ry, TOROW(sd),
r, IF(ry, sd),
c, IF(sd, ry),
res, MAP(r, c, LAMBDA(x,y, SUM((x=oy)*(y=sy)*s))),
tr, BYROW(res, LAMBDA(x, SUM(x))),
trRes, VSTACK("Totals", tr, SUM(s)),
tc, BYCOL(res, LAMBDA(x, SUM(x))),
tcRes, HSTACK("Totals", tc),
oyL, "OY "&sd,
syL, "SY "&ry,
oyR, HSTACK(oyL, res),
oyRC, VSTACK(oyR, tcRes),
cYL, VSTACK(HSTACK("OY/SY Year", syL), oyRC),
fR, HSTACK(cYL, trRes),
fR)
Let's dissect the formula:
s: Sales data
oy, sy: Order and Ship years
cd: Combined years
ud: Unique years
sd: Sorted years
ry: Row numbers
r, c: Mapping conditions
res: Summation result
tr, tc: Row and Column totals
trRes, tcRes: Labeled totals
oyL, syL: Year labels
oyR: Combined labels and results
oyRC, cYL: Results including totals
fR: The final table result
In crafting formulas, I usually adopt one of two approaches. One aims for brevity, attempting to reduce the formula's length as much as possible. The other, which I've adopted this time, focuses on readability. This approach involves breaking the formula down into discrete, comprehensible steps using the LET function. Each approach has its advantages and is chosen based on the specific circumstances.
As always, I'd love to hear your thoughts on this and welcome any corrections, if needed. Keep up the great work!
Ps: the main part of this formula is coming from @Excellambda suggestion (I tried to simplify and add something more).
Awesome, SoftwareTrain!!!! LOVE it : ) I just posted it in the Download Finished Excel File. Go Team!!!!!!
Cool.✌ for fun 😉:
=LET(
o,YEAR(fSales[OrderDate]),
h,YEAR(fSales[ShipDate]),
s,fSales[Sales],
x,"Totals",
r,SORT(UNIQUE(VSTACK(o,h))),
c,TOROW(r),
u,LAMBDA(x,SUM(x)),
m,MAP(IF(c,r),IF(r,c),LAMBDA(x,y,u((x=o)*(y=h)*s))),
b,BYCOL(m,u),l,BYROW(m,u),
VSTACK(HSTACK("OY/SY Year","SY "&c,x),HSTACK("OY "&r,m,l),HSTACK(x,b,u(m))))