Reply
Member
Posts: 47
Registered: ‎01-11-2017
Accepted Solution

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

 

 


Accepted Solutions
Super User
Posts: 2,139
Registered: ‎09-19-2016

Re: Sale in first three days Dax

[ Edited ]

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!




View solution in original post


All Replies
Super User
Posts: 2,139
Registered: ‎09-19-2016

Re: Sale in first three days Dax

[ Edited ]

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!




Member
Posts: 47
Registered: ‎01-11-2017

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

 

Highlighted
Super Contributor
Posts: 3,697
Registered: ‎07-17-2016

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