cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
baronraghu Member
Member

Sale in first three days Dax

Hi All,

 

I am stuck in the following Dax calculation, hope you could help.

 

I have two tables

 

Table 1 has details of a product launch date

 

ProductLaunch date
Mobile09-08-2017
Laptop01-08-2017
Shirt08-08-2017
Jeans04-08-2017

 

Table 2 on Product sale from launch date

 

ProductDateDaily Sale
Mobile09-08-2017891
Mobile10-08-2017599
Mobile11-08-2017828
Mobile12-08-2017977
Mobile13-08-2017521
Mobile14-08-2017695
Laptop01-08-2017787
Laptop02-08-2017646
Laptop03-08-2017693
Laptop04-08-2017917
Laptop05-08-2017781
Laptop06-08-2017674
Laptop07-08-2017679
Laptop08-08-2017998
Laptop09-08-2017930
Laptop10-08-2017744
Laptop11-08-2017859
Laptop12-08-2017667
Laptop13-08-2017617
Laptop14-08-2017844
Shirt08-08-2017606
Shirt09-08-2017678
Shirt10-08-2017772
Shirt11-08-2017783
Shirt12-08-2017718
Shirt13-08-2017793
Shirt14-08-2017894
Jeans04-08-2017696
Jeans05-08-2017765
Jeans06-08-2017964
Jeans07-08-2017784
Jeans08-08-2017869
Jeans09-08-2017745
Jeans10-08-2017566
Jeans11-08-2017765
Jeans12-08-2017839
Jeans13-08-2017659
Jeans14-08-2017875

 

I am trying to find out the sale for each product in the first 3 days from launch.

 

I have used this formula to calculate

CALCULATE([Total Earning],FILTER(ALL(Table2[Date]),MAX(Table1[Release Date])<=Table2[Date]+3)) something similar to Sumifs we do in normal excel

 

Request your help

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Sale in first three days Dax

Hi @baronraghu,

 

I have made a setup of ytou table in wich the tables are related by the Product and added the following measure:

 

Sales_3 days =
VAR date_launch =
    MAX ( Product_Lauch[Launch date] )
RETURN
    CALCULATE (
        SUM ( Product_Sales[Daily Sale] ),
        Product_Sales[Date]
            <= date_launch + 3
    )

This gives me the following result:

 

3days.png

 

I have added the launch date in the summary table but you can you your measure direclty in a chart/card/etc.. no need to have the date or any other filter as you can see above.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
Highlighted
Super User
Super User

Re: Sale in first three days Dax

Hi @baronraghu,

 

I have made a setup of ytou table in wich the tables are related by the Product and added the following measure:

 

Sales_3 days =
VAR date_launch =
    MAX ( Product_Lauch[Launch date] )
RETURN
    CALCULATE (
        SUM ( Product_Sales[Daily Sale] ),
        Product_Sales[Date]
            <= date_launch + 3
    )

This gives me the following result:

 

3days.png

 

I have added the launch date in the summary table but you can you your measure direclty in a chart/card/etc.. no need to have the date or any other filter as you can see above.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




baronraghu Member
Member

Re: Sale in first three days Dax

Hi @MFelix

 

Thanks for your reply.

 

I was getting this error that Calculate function is returning a true/false value which is not allowed. Is this because I am not using DAX studio and missed out something?

 

I had written Calculate (sum(table2(daily Sales)),Table2[Date]),Table2<=Max(Table1[Launch date])+2)

 

I tried using this formula and it worked out for me 

 

Calculate( Sum(Table2(Daily Sale)),Filter(All(Table2[Date]),Table2<=Max(Table1[Launch date])+2)

 

Regards

 

v-ljerr-msft Super Contributor
Super Contributor

Re: Sale in first three days Dax

Hi @baronraghu,

 

Great to hear the problem got resolved! Could you accept the corresponding reply as solution to close this thread? Smiley Happy

 

Regards

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 134 members 1,855 guests
Please welcome our newest community members: