Need help with sales in first 7 days

[hunghahuy]

Hello,

I can calculate the sales in first 7 days, following the dax formula like this:

However, when I want to see which is the product during this first 7 days of each customer, I have to select both customer name and the sales day during first 7 days → to return the name of the product

Could anyone help me with dax formula that allow me to get the product name in first 7 days when I click on each customer name only (& does not have to choose the date in the table 2)

0. Practice DAX Mastering 2A.pbix (450.7 KB)

Thanks in advanced

[AntrikshSharma]

@hunghahuy 0. Practice DAX Mastering 2A.pbix (481.1 KB)

Total Transaction in First 7 Day Ant = 
VAR First_Date = 
    CALCULATE ( 
        MIN ( 'Sales Orders'[OrderDate] ), 
        REMOVEFILTERS ( Products ) 
    )
VAR FirstDates = 
    FILTER ( 
        Dates,
        Dates[Date] >= First_Date 
            &&  Dates[Date] <= First_Date + 7
    )
VAR Result = 
    CALCULATE ( 
        COUNTROWS ( Products ),
        KEEPFILTERS ( 
            CALCULATETABLE ( 
                DISTINCT ( Products[Product Index] ),
                FirstDates,
                CROSSFILTER ( 'Sales Orders'[Product Index], 'Products'[Product Index], BOTH )
            )
        )
    )
RETURN 
    Result

[Harsh]

Hello @hunghahuy,

Thank You for posting your query onto the Forum.

While my colleague @AntrikshSharma has already posted a solution, I just wanted to share my solution.

I’ve worked it in couple of ways i.e., one solution is in the form of “Total Transactions In First 7 Days (In Terms of Products Count)” whereas the other one is in the form of “Total Transactions In First 7 Days (In Terms of Products Names)”. Below are both the DAX measures alongwith the screenshot of the final results provided for the reference -

Products In First 7 Days - Product Count = 
SUMX(
    ADDCOLUMNS(
        FILTER(
            SUMMARIZE(
                'Sales Orders' , 
                'Sales Orders'[OrderDate] , 
                Dates[Date] , 
                Customers[Customer Names] , 
                Products[Product Name] ) , 
            Dates[Date] >=  
                CALCULATE( MIN( 'Sales Orders'[OrderDate] ) , 
                    ALLEXCEPT( 'Sales Orders' , Customers[Customer Names] ) ) &&
            Dates[Date] <= 
                CALCULATE( MIN( 'Sales Orders'[OrderDate] ) , 
                    ALLEXCEPT( 'Sales Orders' , Customers[Customer Names] ) ) + 7 ) , 
        "@Product_Name" , 
        1 ) , 
    [@Product_Name] )

Products In First 7 Days - Product Names = 
IF( ISINSCOPE( Customers[Customer Names] ) , 
    CONCATENATEX(
        TOPN(
            15 , 
            ADDCOLUMNS(
                FILTER(
                    SUMMARIZE(
                        'Sales Orders' , 
                        'Sales Orders'[OrderDate] , 
                        Dates[Date] , 
                        Customers[Customer Names] , 
                        Products[Product Name] ) , 
                    VAR _Min_Date = 
                    CALCULATE( MIN( 'Sales Orders'[OrderDate] ) , 
                            ALLEXCEPT( 'Sales Orders' , Customers[Customer Names] ) )

                    RETURN
                    Dates[Date] >= _Min_Date &&
                    Dates[Date] <= _Min_Date + 7 ) , 
                "@Product_Name" , 
                CALCULATE( MIN( Products[Product Name] ) ) ) , 
            [@Product_Name] , 
            ASC ) , 
        Products[Product Name] ,
        " , " ) , 
    BLANK() )

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this helpful.

Important Note: In your case, the figure for “Total Transactions for First 7 Days” is showcasing as 67 whereas the actual number of records for those transactions is 104.

Thanks and Warm Regards,
Harsh

Total Transactions In First 7 Days - Harsh.pbix (454.5 KB)

[hunghahuy]

Thank you @AntrikshSharma

[hunghahuy]

Thank you @Harsh

But I wonder why the total in the second table (that has Product in first 7 Days - Product Counts) is different from the total in my measure “Total Transaction in First 7 Days”

Did I get the total wrong? Please correct me

Thank you

[Harsh]

Hello @hunghahuy,

Yes, the total in the measure - “Total Transaction in First 7 Days” is incorrect because even if, you export the data to cross-verify and take a sum of that column then the total will be 104 and not 67.

The reason why, you’re seeing the total as 67 is because at a grand total level there’s no context transferred and therefore, it took the First Date at an overall level i.e., 14-Aug-2019 and added 7 days to it. So it created a range from 14-Aug-2019 to 21-Aug-2019.

To cross-check the above explanation, go to the “Sales Orders” table and select the range from 14-Aug-2019 to 21-Aug-2019 and you’ll see 67 rows of data being filtered. Below is the screenshot provided for the reference -

In case, you would like to learn more about fixing totals. Below is the link of the post provided for the reference pertaining to the topic - “Fix Incorrect Totals”.

Thanks and Warm Regards,
Harsh

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[hunghahuy]

Thank you so much @Harsh

Now I understand my mistake

Thanks for your help

Best Regards
Hung

[Harsh]

Hello @hunghahuy,

You’re Welcome!

We’re glad that we were able to assist you.

Thanks and Warm Regards,
Harsh