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

Table of Contents

 

 

 

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 the sum of all the values corresponding to date 21-03-2020 which is the 2nd most recent date.

Solution

 

Step 1: Prepare the dummy table

 

 
If you are already facing a similar problem then you will already have the data but if you are practicing for 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 the 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

 
Categories
quick-report
Power BI Desktop
Kaushlendra Mishra

How to create a report quickly in Power BI Service?

Do you want to create a quick report in Power BI? The latest Power BI version has made it possible to create a quick report using data from the web and existing published datasets in Power BI Service. So, now you do need to download the Power BI Desktop software into your PC and directly make a report using the Power BI service.

Read More »
WHAT IS AVAILABILITY SET & AVAILABILITY ZONE
Azure
Kaushlendra Mishra

What is Availability Set and Availability Zone in Azure?

What is Availability Set and Availability Zone in Azure? In Availability Set, VM`s copy is created and is isolated with separate physical servers, computer racks, storage units, network switches in a data center within a region so if one of the VM stops working then still we have the copy of the VM working within the same datacenter. While Availability Zone ensures that complete datacenter data is replicated to another place at a distance within the same region so that even if complete data center fails due to any reason then also Availability of data is ensured.

Read More »
prepare-DP-200-exam
Azure
Kaushlendra Mishra

How to Prepare for DP-200 Exam?

I have passed the DP-200 exam on 28 Oct 2020. I have gone through the various study material and courses in order to prepare for the exam. Overall, the time taken by me for the DP-200 exam is approx 3 months. Ideally, you should not take 3 months of time but due to lack of guidance, I happen to take a number of online courses which was not required to be taken for passing the DP-200 Exam.

Read More »

Leave a Comment

Your email address will not be published. Required fields are marked *