DAX Variables in PowerBI-Subtracting Prior Value from Current Value of a Column


My Friend got a Scenario where he was asked to subtract the prior value from the current value of the column. I helped him by finding the Solution on the Microsoft community I did not find the correct solution on it.


I have prepared the demo for describing the solution for the problem "Subtracting Prior Value from Current Value of a Column in Power BI "

So Let`s Start!!!




Step 1: I have created a dummy table "YearRevenue Table" with columns Revenue and Years.



Step 2: Now Create a Measure in the same table and place the DAX code mention below:


Diff = VAR Currentyear= MAX('YearRevenue Table'[Years])
VAR PreviousYears=MAX('YearRevenue Table'[Years])-1
VAR CurrentValue=CALCULATE(SUM('YearRevenue Table'[Revenue]),'YearRevenue Table'[Years]=Currentyear)
VAR PREVIOUSVALUE=CALCULATE(SUM('YearRevenue Table'[Revenue]),'YearRevenue Table'[Years]=PreviousYears)
VAR result= CurrentValue-PREVIOUSVALUE
return result



Step 3: After creating a Diff measure using the dax code, Place all the columns in the table
in Power View.

I hope the solution will help the developer to implement it in their report.

DAX Variables in PowerBI-Subtracting Prior Value from Current Value of a Column


Power Query M Language-Convert Seconds to HH:MM:SS Format in Power BI



I got a scenario to convert the Seconds to HH:MM: SS in Power BI.

Step 1: Click on Edit Query and go to the query having seconds in the Column. Below is the screenshot of the Query. The table is "TimeTable" and Column having second Value is "Seconds"

Seconds to HH:MM:SS In Power BI


Step 2: Go to Add Column Tab and create a custom column  and write the M Script Code #duration(0,0,0,[Seconds])


M Script code for converting Seconds to HH:MM:SS
Step 3: You will be getting values like the below screenshot in the new custom column.


Step 4:  Close the Power Query and come to Data Tab in Power BI Desktop. You will now see the values in Time Column in Decimal Format.


Step 5: In Data Tab, Go to Modelling Tab and change the Data type of Time Column to  "Time" and format to HH:MM: SS.


As per the above screenshot, You have successfully converted the Seconds to HH:MM: SS time format.

You can also change the Seconds to HH:MM: SS time format using DAX using simple mathematical calculation but that process is a little complex than the process mentioned above.

Please do comment if you find any error in this article or able to find any other way to achieve the same result.

Thank you.




Power Query M Language-Convert Seconds to HH:MM:SS Format in Power BI


Microsoft Power BI Desktop: Difference between Power Query, Power Pivot, and Power View

Are you beginning your Career Journey with Microsoft PowerBI? Do you want to learn Power BI?

Well here in this article, I will explain about Microsoft Power BI and its essential components which are essential to understand in order to work on Power BI Desktop.

Microsoft Power BI Developers who are not from an excel background are often confused about PowerPivot, Power Query, and Power View.

Microsoft PowerBI is an integrated platform that is made from components of Excel BI toolkit such as Power Query, Power Pivot, and Power View.

Do not worry, If you do not know much about these components beforehand. I have tried to explain each of these in great detail.

Power Query:

Power Query is the ETL Component that helps the developer to extract, transform and load the data into PowerBI.


There are a lot of transformation options provided in order to transform your data and prepare it for data modeling.

PowerQuery-Power Pivot-Power View
Ribbon Bar

Some of the common transformation options provided in the Power Query are listed below:

-Group by
-Use First Row as Header
-Transpose
-Reverse Rows
-Count Rows
-Change Data Type
-Rename Column
-Split Column
-Apply Statistical Function/ Math Functions / Trigonometry Function
- Keep Top Rows/ Keep Bottom Rows
- Remove Columns
-Merge Queries
-Append Queries

With the help of Power Query, You can extract data from multiple sources such as File, Databases, Azure products, web sites and a lot of modern platforms.



PowerQuery-PowerPivot-Power View

There is three connection type through which you can extract data into Power Query.

1. Import
2. Direct Query
3. Live


Import Connection-

Microsoft Power BI default connection type is the Import connection. Import is the only connection type that will allow you to leverage the full capability of the Power BI desktop. Using import data is pulled from the data source connected and stored and compress the data into the PBIX file.

Using import, you can use a complete power query for mashing up the data from various data sources. Once you come out of the Power Query data is loaded to PowerBI frontend into the Vertipaq engine.

Import connection is generally suitable when data volume is little around 5 million because as the size of data increases it becomes difficult to handle the data in the PBIX file and the system used to get hanged.

DirectQuery Connection-

In Direct Query, Data remains within the data source only some queries are sent to the database to return data in order to show Visualization. 

There are some pros and cons while working with Direct Query which is very much important to understand when to use it and when not to use it. 

Pros of Using Direct Query

1. With Direct Query, data is not stored in the PBIX file so there is not to place any schedule refresh. However on-premise gateway is required in order to establish a connection with the data source.

2. In Nov 2017, the Query Reduction feature has been added to Power BI Desktop. This helps to reduce traffic and improve the connection method exponentially. This allows enabling an "Apply Button"  and sends the query to the database only once. Before this method, Query to the database was sent on each selection. 

powerquery-powerpivot-powerview


Cons of Direct Query

1. When Direct Query is used, you cannot mash up much of the data in the power query because it is assumed that you would have already done it in the backend. Even if you don't receive any error in power query, you can receive it once you come out of it and go modeling tab to make the data model.

2. Data Tab is also disabled in the Power View. You have to make sure that all the data transformation has been already done in the backend.

3. Time Intelligence function is unavailable in Direct Query mode.

4. There is a limit of one million row limit on returning data for showcasing Visualization unless you have a premium subscription.


Live Connection

Live Connection is provided by SSAS ( SQL Server Analysis Service ) in order to connect it with Power BI.

The live connection allows you to use your data model for multiple reports thus leveraging the use of the data model created in SSAS.

Pros of Live Connection 

1. The central model that supports many reports. I have explained above how Data Model created in SSAS can be used for multiple reports.

2. No Memory or Size Constraints in Power BI

3. More Secure and Better Security


Cons of Live Connection

1. It disables most of the Power BI features. Data Pane, Relationship Tab, Dax Tables, Calculated Columns, M/ETL/Power Query are all gone like as it is in Direct Query.

It is expected to do all transformation, ETL operations within the SSAS or at the database level.

However, From May 2017 Microsoft allow us to use calculated measure with a live connection.


Power Pivot


Power Pivot is the component where you can establish a relationship between various Tables/Queries loaded into the data model. 

PowerQuery-PowerPivot-Power View


Power Pivot also includes DAX so you can make changes to data outside Power Query.


Power View 

Power View is the component where you make all your Visuals.  Here you use bookmarks for Page Navigation, Slicer Panel Creation, etc. 

PowerQuery-PowerPivot-Power View



I hope that now these terms will be clear to you all.



Thank You.




You may also like:

Top Websites for Free Public Data Sets for Machine Learning and Data Science Project

DAX Variables in PowerBI-Subtracting Prior Value from Current Value of a Column

Power Query M Language-Convert Seconds to HH:MM:SS Format in Power BI

As per Gartner Magic Quadrant Analytics and Business Intelligence platform Report, Microsoft Power BI leads in 2020

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub



Microsoft Power BI Desktop: Difference between Power Query, Power Pivot, and Power View