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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.