Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Sofiane85
Frequent Visitor

sales from different dates range

Hi all,
i have 2 tables, "Sales" and "Target"
in the Target table there is some of products and 2 dates columns : Start Date and End Date,
please, i need to calculate the sales from "Sales" table, only for dates that are between (>=  <=) the start date and end date, and only for the products that are in the Target Table (not all products of sales table)
the result needed in image
Capture d'écran 2023-12-07 140831.png

 

Target test 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Sofiane85 

Add following culumn to your target table:

Sales = 
VAR __Prod = Target[Product] 
VAR __DateStart = Target[start date] 
VAR __DateEnd = Target[end date] 
VAR __Result = 
SUMX(
    FILTER(
        Sales,
        Sales[Product] = __Prod &&
        Sales[Date] >= __DateStart && Sales[Date] <= __DateEnd 
    ),
    Sales[Qte]
)
RETURN
    __Result

 

Fowmy_0-1701955879846.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@Sofiane85 

Add following culumn to your target table:

Sales = 
VAR __Prod = Target[Product] 
VAR __DateStart = Target[start date] 
VAR __DateEnd = Target[end date] 
VAR __Result = 
SUMX(
    FILTER(
        Sales,
        Sales[Product] = __Prod &&
        Sales[Date] >= __DateStart && Sales[Date] <= __DateEnd 
    ),
    Sales[Qte]
)
RETURN
    __Result

 

Fowmy_0-1701955879846.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy  thank you so much;

it works perfectely.
please one more thing if you want, how i can visualize this by "Seller" (i dont have a relationship between the 2 tables)
thank you again

@Sofiane85 

The Seller column is in the Sales table, what is your question exactly? 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

exactely, the seller is in the Sales table, and i want to visualize the sales by Seller and product in the date ranges 
and image for exemple
test seller.png

@Sofiane85 

You need to specify the logic to allocate the sales against the sellers. looking at your data, there is not target defined by seller, only by product level.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

thank you so much my friend, you helped me a lot, i adapted your measure to the sales table, and it works 👍
than you again

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors