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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors