Tag: Productivity

DAX Fridays! #127: Calculate Same period last Month in DAX

Posted on by Annie D

Comparing periods is something you should always do as it adds context to your data, but how do
you calculate Same period last month? PREVIOUSMONTH function will give you the entire previous month, so how do you do it?
#powerbi #curbal #dax #daxfridays

Here is one of the many ways to do it.

Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU

Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#daxfridays #curbal #dax #powerbi #mvp

View on YouTube

MSPTDA 23: Two Fact Tables? DAX, Power Query or Worksheet Formulas to Convert to 1 Fact Table

Posted on by Annie D

All 9 files for video in this zipped folder: http://bit.ly/2JGTGfd
Download file individually at class web site: http://bit.ly/2GF25Ni
pfd notes for Video #23: http://bit.ly/2JGaCTb

In this video learn about how to convert 2 Fact Tables into one with DAX, Worksheet Formulas or Power Query
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:16) Introduction

2. (02:35) Excel Worksheet Formula Solution
3. (05:01) How do we allocate Discount from Invoice Grain to Invoice Line Grain?
4. (05:45) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function
5. (09:23) Worksheet Formula for % Sales Discount at Invoice Grain using division
6. (10:00) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication
7. (12:12) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?
8. (13:27) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication
9. (14:30) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS
10. (15:04) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication
11. (16:50) Create Excel Reports at Product Grain.
12. (17:03) Standard PivotTable Report
13. (17:53) Worksheet Formula Report

14. (21:26) DAX Formula Solution in Power Pivot
15. (22:30) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE
16. (24:30) Bring Excel Tables into Data Model
17. (25:15) Create Relationships between tables
18. (26:23) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers
19. (26:56) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions
20. (29:40) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function
21. (30:50) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication
22. (31:57) DAX Measure for Total Discount
23. (32:39) Data Model PivotTable Report for Product Discount
24. (33:08) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+
25. (34:22) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED.
26. (35:56) Visuals to understand how DAX Formula with SUMX, REALTEDTABLE and RELATED are working to traverse multiple relationships in one formula. This helps illustrates the Power of DAX for Business Calculations.
27. (36:52) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula
28. (39:03) DAX Measure for Total Shipping
29. (39:25) Final Data Model PivotTable

30. (40:22) Power Query Solution in Power BI Desktop
31. (41:09) Why Two Fact Tables will not work with all Dimension Tables for Reporting.
32. (43:05) Summary and visuals of steps we need to perform
33. (44:12) Create blank Power BI Desktop file
34. (44:40) Import Two Fact Table Data Model from Power Pivot
35. (45:40) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function
36. (46:53) Power Query Merge to lookup Product Weight at Invoice Line Grain
37. (47:26) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function
38. (47:42) Power Query Group By feature to aggregate Invoice Sales, Invoice Shipping Weight and all rows in Invoice Line Grain Table for each Invoice Number.
39. (49:52) Power Query Merge to pull Invoice Grain Shipping & Discount numbers, as well as to pull the Invoice Level Dimensions of Date and Sales Rep ID into the current step in the query (later after expanding it will be the Invoice Line Grain).
40. (50:52) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function
41. (52:11) Expand to get back to Invoice Line Grain
42. (52:31) Note about Unit Price and how it is stored as a Fact because it changes so often.
43. (54:16) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round
44. (54:49) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round
45. (57:23) Remove all column we do not need in final Fact Table
46. (58:08) Load Tables to Data Model, except Invoice Level Table.
47. (59:15) Create DAX Measures for Shipping, Discounts and Sales
48. (01:00:10) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function.
49. (01:01:15) Hide Columns from Report View
50. (01:01:33) Look at Final Data Model
51. (01:01:47) Create Visualization in Power BI Desktop
52. (01:04:40) Summary

View on YouTube

rank in Access

5 Lifesaving ⛑ tips in Power Bi you might not know

Posted on by Annie D

You know how this works by now right? If I learn something useful, I share it here with you and in today’s video I am going to share my top 5 power bi tips to fix current issues and cool functionality.

Did I manage to surprise you with a tip you didnt know? #curbal #powerbi #5tips

Keynotes:
1. Word wrap, mini map and more power query
2. Copy formatting in a visual
3. Fix non-responsive/ freeze power bi ribbon
4. Bookmark filter pane status
5. Copy buttons with actions.

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#POWERBITIPS #CURBAL #POWERBI #MVP

View on YouTube

[FREE EXCEL COURSE] Lesson 17 – Named Ranges in Excel

Posted on by Annie D

In this video of this online Excel Training, you will learn about using Named Ranges in Excel.

It covers the following topics:

— Creating Named Ranges
— Managing Named Ranges
— Important Keyboard Shortcuts
— Creating Dynamic Named Ranges using OFFSET and INDEX formulas

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

TrumpExcel.com | Online Excel Tips & Tutorials (with videos)


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

How to Merge Excel Files with WBMerge

Posted on by Annie D

To buy or try WBMerge: http://bit.ly/2WH82Qt
Save 20% on WBMerge through June 30 2019 using discount code MREXCEL

In this video, I try the WBMerge utility on a collection of Excel workbooks that might be problematic in Power Query.

WBMerge is a $39 utility from Workbook Tools. The utility can merge data from multiple Excel workbook or multiple Google Sheets files.

View on YouTube

Export one table of data or the entire model with DAX Studio

Posted on by Annie D

In today’s video I am going to show you how to export data from one table or the entire model using DAX Studio.
#curbal #dax #daxstudio

Keynote:
02:00 Download data from one table with DAX Studio
02:40 Download the entire model with DAX Studio

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

View on YouTube

DAX Fridays! #126: Understand the Formula Engine and Storage Engine to optimize your DAX queries

Posted on by Annie D

To be able to optimize DAX queries, you need to know how the formula engine and the storage engine work with them, so how about we let them explain themselves?
#curbal #powerbi #dax #DAXFridays

Hope you enjoy the video, i had fun doing it 🙂

Have you missed the Vertipaq series? Here are the videos:
Part1: In this video, I will go through the main known things that Vertipaq does to compress the data: https://www.youtube.com/watch?v=b8oSgTOeYCE

Part2: In this video, we will go through what it means that Vertipaq is a columnar database and why performance is so good: https://www.youtube.com/watch?v=A461ktvExH0

Part3: Why is Vertipaq so fast? Segmenting the data is one of Vertipaq tricks to manage data at blazing fast speeds. In this video, I will explain what that is: https://www.youtube.com/watch?v=A67tsJ03Afs

Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU

Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#daxfridays #curbal #dax #powerbi #mvp

View on YouTube

DAX Fridays! #4: COUNT, COUNTA, COUNTX, COUNTAX, DISTINCTCOUNT AND COUNTROWS in DAX

Posted on by Annie D

Let’s go through all the count expressions available in DAX!

Here you have the keynotes for the video so you can jump to the function you want to learn:

02:44 COUNT text (#ERROR)
03:24 COUNT numbers
04:20 COUNT blanks
04:56 COUNT dates
05:36 COUNTA text
06:44 COUNTA numbers
07:27 COUNTX
09:16 COUNTAX
09:58 DISTINCTCOUNT
10:59 COUNTROWS(DISTINCT)
12:06 COUNTBLANK
12:34 COUNTROWS
13:32 COUNTROWS with columns (#ERROR)
14:33 COUNTROWS with an expression

Link to Curbal Glossary: http://bit.ly/2JbxKt8

PREVIOUS VIDEO: https://www.youtube.com/watch?v=vcYz0XPklXg
NEXT VIDEO: https://www.youtube.com/watch?v=-oDpOfhgmzA

Dowload the sample file here:
http://bit.ly/2Jv3RDN

Looking for a download file? Go to our Download Center: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: https://goo.gl/rvIBDP
► Facebook: https://goo.gl/bME2sB
► Linkedin: https://goo.gl/3VW6Ky

#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP

View on YouTube

Cross Selling Matrix Deep Dive – Power BI & DAX Tutorial

Posted on by Annie D

In this tutorial we cover how to create a cross selling matrix.

This requires a full understanding of the concept of ‘context’ and much more.

Some advanced DAX formulas are covered in detail, especially table functions.

Plenty of great techniques to learn about during this one.

Enjoy!

Sam

***** Learning Power BI? *****

FREE COURSE – Ultimate Beginners Guide To Power BI – http://bit.ly/2SGof6C

FREE COURSE – Ultimate Beginners Guide To DAX – http://bit.ly/2AyEb3P

FREE – Power BI Resources – http://bit.ly/2C2aBn3

FREE – 60 Page DAX Reference Guide Download – http://bit.ly/2AyEc7T

Learn more about Enterprise DNA – http://bit.ly/2RacRD3

Enterprise DNA Membership – http://bit.ly/2AAqa5u

View on YouTube

Microsoft Excel 2016 Tutorial For Beginners Part 2 Full Intro Learn How to Use Excel 2016

Posted on by Annie D

This is a Microsoft Excel 2016 Full Tutorial for beginners & intermediate users.In this crash course Excel video tutorial you will learn how to use Excel 2016 formulas and functions, Excel 2016 new features, Excel 2016 Charts along with many other necessary features that you must know how to use when using Excel 2016.
Excel Charts Tutorial
How to Excel charts
Formatting Excel Chart
Chart Styles in Excel 2016
Microsoft Excel 2016 Tables Tutorial
Excel tables tutorial
how to Excel Tables
Excel Tables for beginners
How to insert pictures in Excel
How to insert word art in Excel
Insert shapes in Excel

http://bit.ly/2f0EiJ7
hjttp://roofstampa.com
http:/www.subjectmoney.com
http://bit.ly/1jhTIZH

View on YouTube

[FREE EXCEL COURSE] Lesson 16 – 10 Advanced Examples Formula Examples

Posted on by Annie D

There a lot of awesome stuff you can do with a combination of formulas in Excel.

In this video, I will show you some advanced formula examples that involve using one or more than functions.

I cover the following topics this video:

— Compare Lists, Get Unique List
— Get the Closest Match
— Get Last Value in the List
— Find the Last Occurrence of an Item in a List
— 2 Way and 3 Way Lookup using Index/Match
— Count the Number of Words
— Extract Username from Email Id
— Find the First Monday of the Month
— Extract Data using Drop Down List

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

TrumpExcel.com | Online Excel Tips & Tutorials (with videos)


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

[FREE EXCEL COURSE] Lesson 15 – Date & Time Formulas in Excel

Posted on by Annie D

In this video of this online Excel Training, you will learn about the following DATE and TIME formulas:

— DAY function
— HOUR function
— MINUTE function
— DATE function
— DATEVALUE function
— TODAY function
— NOW function
— WEEKDAY function
— NETWROKDAYS function
— NETWORKDAYS.INTL function
— WORKDAY function
— WORKDAY.INTL function

This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

TrumpExcel.com | Online Excel Tips & Tutorials (with videos)


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube