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 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



No comments