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
Irache
Helper II
Helper II

IN/OUT calcule

Hello,

I want to calculat the IN and OUT of my stock for 1 week.
I have already try that but it's more complicated than we think.

For exemple I have this table:

ArticleName articleWeek InWeek out
9BMotor2023-012023-03
9Dwheel2023-01 
9Gdrive shaft2023-012023-03
9RPinion2023-012023-01
9Ereserve2023-01 

 

In this case I want to count all OUT in 2023-03 week and all IN in 2023-03 week. After that I want to calcule CURRENT measure which equals the sum of all IN <= current week less than the sum of all OUT <= current week.
If we take the 2023-01 week we obtain: 5 IN & 1 OUT so CURRENT = 4.
I hope than someone can help me because I block on this problem and I don't find a solution.

Sincerly,
Imad RACHATI.

10 REPLIES 10
Imad-R
Frequent Visitor

Yes, I have a week table.

would you mind providing the sample data for it here?

I want to send the files but I don't know how I can send it ?

I have add 2 date column in Data table and that is a sample of Date table :

DateYEAR-WEEK
01/01/202 00:002023-01
02/01/2023 00:002023-01
03/01/2023 00:002023-01
04/01/2023 00:002023-01
05/01/2023 00:002023-01
06/01/2023 00:002023-01
07/01/2023 00:002023-01
08/01/2023 00:002023-02
09/01/2023 00:002023-02
10/01/2023 00:002023-02
11/01/2023 00:002023-02
12/01/2023 00:002023-02
13/01/2023 00:002023-02

 

and this is DAX code :

Date = ADDCOLUMNS(
    CALENDARAUTO(),
    "YEAR-WEEK", YEAR([Date])&"-"&RIGHT("0"& WEEKNUM([Date]),2))
 In addition the link between the table.
ImadR_0-1678744264839.png

 

Wait what?  Is it Week-in or Date-in in your fact table?

They have week IN and date IN in my fact table I have modified this table to match with my problem. They have two temporal dimension in this table because I must have the Date IN for have the Week IN and the Date OUT for the Week OUT. 

 

The Date IN column are link with my Date table but I don't know if it's good to link with this column ?

oh yes, it is much better than having to link via the week number. You always want to link at the lowest possible granularity level to avoid M:M relationships.

I have find a solution for my problem.

lbendlin
Super User
Super User

Do you have a Week table in your data model?

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.