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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors