While going through the Microsoft Power BI Community, I saw a post where a fellow member was asking for a solution to the problem in getting the difference between the sum of values for the most recent date and Just the previous date.

I worked to find a solution for the problem and found that the solution was interesting for people who would like to play with DAX.


Problem Statement

To find the difference between the sum of the value for the most recent date and 2nd most recent date.



Here, we have to find the difference between the sum of all the sales values corresponding to date 23-03-2020 which is the latest date and sum of all the values corresponding to date 21-03-2020 which is 2nd most recent date.


Solution



Step 1: Prepare the dummy table



If you are already facing the similar problem then you will  already have the data but if you are practicing for the experiencing the fun of dax then you can create the data by pressing the ENTER DATA in the Home Ribbon.



Step 2:  Write the DAX measure for finding the sum of Sales value for most recent date.


Current sales sum =
VAR MAX_DATE =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[Date] = MAX_DATE )


Step 3: Write DAX measure for getting the 2nd most recent date so that we can create another measure for finding the sum of value for the 2nd most recent date.


2nd most recent date =
VAR current_date =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < current_date )


Step 4: Write DAX measure for finding the sum of values for the 2nd most recent date.


2nd most recent date Sales =
VAR previous_date_just = [2nd most recent date]
RETURN
    CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[Date] = previous_date_just )


Step 5: Write DAX Measure for finding the difference between sum of values corresponding to most recent date and 2nd most recent date.


Difference =
[Current sales sum] - [2nd most recent date Sales]



Demo



Difference between sum of values for recent date and 2nd most recent date.



While working on the scenario "How to refresh dataset using button in Report view of Power BI?", We have added a DAX function UTCNOW() which will give the refresh time and date value in a card. But the value in the card will be in the UTC and we are more interested to show the value in the timezone depending upon the report user location.


For example, If report users are in India so I  have to show the time in  Indian standard time (+5:30 UTC). So in this article, we will see how to convert UTC to Local Time Zone using DAX in Power BI Report.

In order to convert UTC to local time zone,

1. We need to create a measure showing the Date and Time value in the UTC format.

UTC =
UTCNOW ()

If we place the measure in a card then we will get the Date and Time value as per the Universal Time Coordinated (UTC)

How to convert UTC  into Local Time Zone in Power BI Report using DAX?


2. We need to convert the UTC time zone format to the IST time zone as our users are located in India.

In order to convert we need to create a measure IST using below mentioned DAX Code.


ITC =
FORMAT ( [UTC], "mm/dd/yyyy hh:nn:ss AMPM" ) + TIME ( 53000 )


Format Function helps to format the UTC date and Time Data in the format provided in the function. and + TIME ( 53000 ) add the 5 hours and 30 minutes to the UTC time in order to convert to the IST time zone.


So let`s see the result of the IST measure created.




Final Thoughts

Conversion of the time zone is a very practical requirement that is used very frequently. Even though there are other methods as well through the power query but I found this method very easy to implement.

Please do let me know your thoughts and if you like the article please share with your friends on Linkedin.

How to convert UTC into Local Time Zone in Power BI Report using DAX?




You can add a button in your report which will refresh the data once you click on the refresh button in the report view. I have seen in various forums that this has been asked by many clients. For achieving this functionality we will use Microsoft Power Automate and also use Wrap API.

How to refresh dataset using button in Report View of PowerBI?



Drill-through is an option in power bi desktop using which we can drill through to a page having details with a specific context. After the March 2020 update, Drill through can be performed using the button as well, earlier the same task was performed by right-clicking on a context in a chart and then selecting the drill through option.

How to perform drillthrough action using button in Power BI?



In this article, we will discuss dynamic multiple column selection using a slicer and a button in Microsoft Power BI. Here, we will use the new page navigation option which is provided action property of the button. Page Navigation in action property is available from Power BI March 2020 update. We have already explained it through an article, you can check it to know more about it.

Dynamic Multiple Column Selection in Power BI





Page Navigation is an important part of the report as it makes the report more interactive. Previously, We used to achieve page navigation in a report using a bookmark and it was not possible to do the same using slicers and a  button. After the March 2020 Power BI Update, It is possible to do page navigation using a slicer and a button.

How to do Page Navigation using Slicer and a Button (without bookmark) in Power BI?

Power BI : How to get data from latest file in a folder in Power Query?



Working on data from the latest file in a folder is a very common requirement. I have been asked about it in one of the Company Interviews. Well, at that moment of time I was having no idea about it but later I got this as a requirement in one of my project requirements.

So, How do we get data from the latest file in a folder in Power Query?  Check the below steps and you can practice with me in your machine. Create a dummy folder with various excel files and follow the steps which I have mentioned below.


 1. In Microsoft PowerBI Desktop, Click on Get Data >> Folder and click on the connect button at the bottom.


Power BI : How to get data from latest file in a folder in Power Query?



2. Input the folder path from where data from the latest file need to be brought in Power BI and click on the OK button. 


Power BI : How to get data from latest file in a folder in Power Query?


3. In the pop-up windows, click on the Transform button and then Power Query will get opened.



Power BI : How to get data from latest file in a folder in Power Query?



4.  In the Power Query, Sort the date column by clicking the down arrow at the right of the column name. Then sort the column in descending order.


Power BI : How to get data from latest file in a folder in Power Query?



5. Click on Reduce Rows Button and select Keep Top Rows and then Enter Keeps Rows as 1. 

Power BI : How to get data from latest file in a folder in Power Query?

      It will now show only the row having the date.

 6. Click on the double symbol on the right of the Content Column. 

Power BI : How to get data from latest file in a folder in Power Query?


7. Select the sheet having Data and click on the Ok button.


Power BI : How to get data from latest file in a folder in Power Query?


8. Now, You have data ready. It is optional to remove the Source. Name Column which shows the file name.

Power BI : How to get data from latest file in a folder in Power Query?


9. Click on Close and Apply button and get out of Power Query.

How to get data from latest file in a folder in PowerBI?


Final Thoughts


It was an easy task to get the data from the latest file from a folder. This scenario is applicable to servers where the middleware messaging solution like MQ has been implemented and lot of files use to come to a folder and we have a requirement to analyze the data of latest file.

I hope that the article was helpful to you. Please comment and let me know your feedback.





How to get data from latest file in a folder in PowerBI?




I recently worked upon a scenario where I have shown all the selected values in slicer in a Card. So, Card basically acts as a display screen which shows all the selected values in the slicer. The scenario which I just mentioned is very relevant and practical from reporting perspective with an intention to show selected values in a particular area of the report.

PowerBI : How to Display multiple selected values in a Card?