Basics in Power BI (No Audio) -- Creating Datamodel and Sample visuals

Following link will show you how to create a data model and couple of sample visuals in Power BI tool.

Note: There is No Audio in this video.

 https://www.youtube.com/watch?v=22dkC6jdY-w

 

DATEADD Function in DAX / Power BI

DATEADD Function in DAX

DATEADD function is used to add days to the specified dates based on the interval we are specifying.

Syntax:

DATEADD(<DATES>,<No_Of_Intervals>,<Interval>)

Parameters:

Dates: It can be any Date or DateTime Column. It can be an expression that returns date or datetime value.

No Of Intervals : It is an integer value which specifies the number to be added or subtracted from the date. If the interval value is positive, it moves the dates forward in time, and if the interval value is negative it moves the dates backward in time.

Interval : The interval by which to shift the dates. The value for interval can be one of the following: year, quarter, month, day. Interval should not be given without quotes.

Return Values:

A table containing a single column with dates.

 Example:

Below expression will move year backward in the current context.

    DATEADD(Orders[Order Date],-1,YEAR)

 

This is useful when we are comparing current year sales with previous year sales.

LY_Sales = CALCULATE(SUM(Orders[Sales]),DATEADD(DimDate[Date],-1,YEAR))

 

DATEDIFF Function in DAX / Power BI

 DATEDIFF Function in DAX

DATEDIFF function is used to get the difference in dates based on the interval we are passing as an argument.

Syntax:

DATEDIFF(<Start_Date>,<End_Date>,Interval)

Parameters:

Start_Date à It can be any Scalar Date Or DateTime value

End_Date à It can be any Scalar Date OrDateTime Value

Interval à It is the interval to be used to compare the dates. Following are the intervals we can use in this argument –

• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• HOUR
• MINUTE
• SECOND

Return Value :

The count of interval boundaries crossed between two dates.

Note: Start_Date cannot be greater than End_Date. It will throw an error if  Start_Date is greater than End_Date.

Examples:

Date_DIFF_In_DAYS = DATEDIFF(Orders[Order Date], TODAY(),DAY) à will return difference in days

Date_DIFF_In_MONTHS = DATEDIFF(Orders[Order Date], TODAY(),MONTH) à Will Return difference in months

Date_DIFF_In_WEEKS = DATEDIFF(Orders[Order Date], TODAY(),WEEK) ) à Will Return difference in weeks

Date_DIFF_In_QUARTERS = DATEDIFF(Orders[Order Date], TODAY(),QUARTER) ) à Will Return difference in Quarters

Date_DIFF_In_YEARS = DATEDIFF(Orders[Order Date],TODAY(),YEAR) ) à Will Return difference in Years

Following is the output of above calculations:

SAMEPERIODLASTYEAR Function in DAX / Power BI

SAMEPERIODLASTYEAR Function in DAX

 

SAMEPERIODLASTYEAR

This function is used to get the dates of the previous year with respect to the specified date column.

Syntax:

SAMEPERIODLASTYEAR(<DATE_Column>)

 

Parameter:

Date_Column – Any column which contains dates. It can be an expression which returns single column of date / datetime values. Or it can refer to any date or DateTime column.

Return value –

It returns a single column table with dates.

Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context

This function works same as DATEADD(Dates,-1,YEAR). SAMEPERIODLASTYEAR function cannot be used in DirectQuery mode.

This function is useful in time series analysis. It will be very useful when we have to compare measures of any time interval with that of corresponding previous year’s same time interval measures.

Example:

Following example will calculate last year’s sales

LY_Sales = CALCULATE(SUM(Orders[Sales]),SAMEPERIODLASTYEAR(DimDate[Date]))

 

CALCULATE is a function which Evaluates an expression in a context that is modified by the specified filters.

Let’s see how we can use in PowerBI

I am taking order table where I have Product Category, Region, Sales amount and Order date as some of the columns. Now, I would like to see Total Sales Amount of all months in the Selected year, previous year and also the difference between current year’s sales and Previous year’s sales for the selected product category, Region and Year.

In order to use SAMEPERIODLASTYEAR function, we have to have a table with contiguous dates. I am going to use DimDate for the same.

Steps:

1.       Create a measure for Total Sales Amount .

Total_Sales = sum(Orders[Sales])

2.       Create another measure for last year sales.

LY_Sales = CALCULATE(SUM(Orders[Sales]),SAMEPERIODLASTYEAR(DimDate[Date]))

3.       Create third measure for the difference between CY and LY Sales

LY_And_CY_Sales_Diff = [Total_Sales] -  [LY_Sales]

4.       Take Line and clustered column visual. Take Month on Shared axis, CY and LY as column values and Difference as Line Value.

5.       Take Year, Region and Category as slicers.

I have created 2 visuals one with Month as Shared axis in the first one and Quarter as Shared axis in the second one.

Appreciate Suggestions and Comments...
 

Generating Calendar (Date Dimension) in Power BI

Generating Calendar (Date Dimension) in Power BI

Many times, we use the date hierarchy in the analysis. Power BI generates the hierarchy for the dates. But, it will generate only the predefined levels in the date hierarchy. It shows Year, Quarter, Month and Day as Below –

But, if we want to have our defined levels, we have to have our own calendar with the specified start date and End Date.

We can use this ADDCOLUMNS function along with CALENDAR function to generate calendar Dimension.

Steps to create the above date dimension in Power BI Model –

1. Open Power BI, Go to Modelling Menu
2. Click on New Table icon in the ribbon
3. Write the following Expression –
   
4. Validate the expression.
5. It will create the date dimension table starting from minimum of order date till today. You can give our own start date and end date in the calendar function.
6. Following is the Data we can see in the dimension -

Thanks....

Please share your Comments and Suggestions....

ADDCOLUMNS Function in DAX

ADDCOLUMNS Function in DAX

This function is used to add calculated fields (user defined) as columns to a table or a table expression in the data model.

 Syntax:

                    ADDCOLUMNS (<table>, <name>, <expression>[, <name>, <expression>]…)

                                It returns the table with the columns of the table along with the derived columns.

Parameters:

Table  - Any DAX expression that returns a table of data.

Name - The name given to the column, enclosed in double quotes.
Expression - Any DAX expression that returns a scalar expression, evaluated for each row of table.

Example:

We have a dataset with Orders of the business in “Orders” and returned orders in “Retruns” with the reference of “Order ID”.

Following example will create a new summary table with the name – “Return_Order_Summary” with sales amount for the respective Order ID.

Data in the table looks like  –

Another Scenario where we can use this Function is – Generating Calendar Dimension

Many times, we use the date hierarchy in the analysis. Power BI generates the hierarchy for the dates. But, it will generate only the predefined levels in the date hierarchy. It shows Year, Quarter, Month and Day as Below –

But, if we want to have our defined levels, we have to have our own calendar with the specified start date and End Date.

We can use this ADDCOLUMNS function along with CALENDAR function to generate calendar Dimension.

Steps to create the above date dimension in Power BI Model –

1. Open Power BI, Go to Modelling Menu
2. Click on New Table icon in the ribbon
3. Write the following Expression –
   
4. Validate the expression.
5. It will create the date dimension table starting from minimum of order date till today. You can give our own start date and end date in the calendar function.
6. Following is the Data we can see in the dimension -

Thanks....

Please share your Comments and Suggestions....

 

CALENDARAUTO Function in DAX

CALENDARAUTO Function in DAX

CalendarAuto Function is used to Generate dates based on the date column of Data Model. It will generate dates from minimum of date value in the date column till the maximum of date.

It will generate based on the parameter value we are passing. It will generate from the month (parameter value) till the respective year end.

Syntax:

CALENDARAUTO([fiscal_year_end_month])

Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.

Parameters:

Fiscal_year_end_month :  Any DAX expression that returns an integer from 1 to 12. If omitted, defaults to the value specified in the calendar table template for the current user, if present; otherwise, defaults to 12.

The date range is calculated as follows:

·         The earliest date in the model which is not in a calculated column or calculated table is taken as the MinDate.

·         The latest date in the model which is not in a calculated column or calculated table is taken as the MaxDate.

·         The date range returned is dates between the beginning of the fiscal year associated with MinDate and the end of the fiscal year associated with MaxDate.

An error is returned if the model does not contain any datetime values which are not in calculated columns or calculated tables.

 

Example:

In the following Example , if the minimum date is 10^th Jan 2016 and the maximum date is 22^nd Feb 2018 in the model, it will generate date from 01-Jan-2016 till 31^st Dec 2018. Since we are not passing any argument, it will take 12 as default which is December.

CALENDARAUTO() -- generate dates from 01-Jan-2016 till 31^st Dec 2018.

 

In the following Example, if the minimum date is 10^th Jan 2016 and the maximum date is 22^nd Feb 2018 in the model, it will generate date from 01-Jan-2016 till 28th Feb 2019. Since we are passing 3 as Argument, it will consider March as the starting of Fiscal year.

CALENDARAUTO(3) -- generate dates from 01-March-2016 till 28th Feb 2019.

 

Calendar Function in DAX

Calendar Function in DAX

Calendar function is used to generate dates between 2 specified dates. It is included in SQL Server Analysis Services 2016, PowerPivot 2016 and Power BI only.

Syntax:

CALENDAR(<Start_Date>,<End_Date>)

Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is from the specified start date to the specified end date, inclusive of those two dates.

Parameters: First one supposed to be the Starting Date from where user wants to generate dates and the second one is the End Date till what date we are generating dates.  

Start_date  à can be any DAX Expression or a column that returns a DateTime value.

End_date  à can be any DAX Expression or a column that returns a DateTime value.

First parameter values should be less than the second parameter value.  It will throw error if the starting date is greater than the ending date.

Example:

In the following example we are going to generate dates from user defined start date and end date.
It will generate dates from 1^st jan 2018 till 31^st Jan 2018. It will create a table with the column name – “Date”.

Cal_User_Dates = CALENDAR (DATE (2018, 1, 1), DATE (2018, 1, 31))

Output will be :

 

We can use the date columns of data model as arguments to start from the minimum date of a date column till the maximum date of the same date column or someother date column.  

In the following example we are going to generate dates from minimum date of Order Date column till the maximum date of Order Date column.

 Cal_Data_Model = CALENDAR(MINX(Orders,Orders[Order Date]),MAXX(Orders,Orders[Order Date]))

It will generate dates from minimum order date till maximum order date. we can check the dates in Cal_Data_Model (which we have generated above) with the help of Cards.

 Following is the result –

Another Example-

Calendar_User_Defined = CALENDAR(MINX(Orders,Orders[Order Date]),TODAY())

It will generate dates from minimum order date till current Date. We can check the dates in Calendar_User_Defined (which we have generated above) with the help of Cards.

Following is the result –

Please leave comments and suggestions....