Sunday, February 18, 2024

How to calculate Max Sale Date and Product details using DAX in Power BI

How to return Max Sale Date and Product with correct Totals using DAX in Power BI
Suppose we have simple Data Modal as follows.



The sample Data from ds_Sample is as follows:

----------------------------------------------------------------
Scenario 1:
Returning the Max Sale Date, and the Product that contributing the Max Sales


We can achieve this Scenario using the following DAX Functions.

Net Sales = SUM(ds_Sample[Qnty_Sold])

Max Sales By Date =
MAXX(
    VALUES(ds_Sample[Order_Date]),  
    [Net Sales]
   /* CALCULATE(SUM(ds_Sample[Qnty_Sold])) */
)

Max Sale Date =
CALCULATE( FIRSTNONBLANK(ds_Sample[Order_Date],1),
    FILTER( VALUES(ds_Sample[Order_Date]),
       [Net Sales] = MAX(ds_Sample[Qnty_Sold])
    )
    )

Max Sale Prod By Date =
CALCULATE( FIRSTNONBLANK(ds_Sample[Prod_Name],1),
    FILTER( VALUES(ds_Sample[Order_Date]),
       [Net Sales] = MAX(ds_Sample[Qnty_Sold])
    )
    )

Result:


Note:
The Total value 174 is the Max Total Sales by Date not the Total of 3 Max Sales.


----------------------------------------------------------------
Scenario 2: 
Returning the Unique Orders and Max Order value by Segment based on Order Date, with a Correct Total.

Max Sales = MAX(ds_Sample[Qnty_Sold])
Unique Orders = DISTINCTCOUNT(ds_Sample[Order_Date])

Note:
Please note that, I have considered the [Order_Date] for the Unique Orders calculation, only to explain this Scenario.

The [Order_Date] Column is having Duplicate Records, as we can expect the multiple Orders on the same date.

Now, to fulfil this Scenario, we will fix the Total Issue for [Max Sales] and [Unique Orders] by using the Measures as shown below:

Max Sales (Total) =
SUMX( VALUES(ds_Sample[Prod_Segment]),
    [Max Sales]
    /* CALCULATE(MAX(ds_Sample[Qnty_Sold])) */
    )

Unique Orders (Total) =
SUMX( VALUES(ds_Sample[Prod_Segment]),
    [Unique Orders]
    /* CALCULATE(DISTINCTCOUNT(ds_Sample[Order_Date])) */
    )

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog