Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Friends!
I´m trying to get the backlog of sales on my area.
I have a table "SALES" with their respective Sales Document. The important fields are:
Pedido - Pos. - Material - Ctd.Ped - Fe.Despacho
It means for this Pedido, Pos, Material the sales must be realized before of 03-03-2020 and with the 60 units.
In other table (MB51), i have the stock movements for a lot of materials with Ped.Clte (same as Pedido), PosPdCl (same as Pos.), Material (same as Material in SALES), Ctd.UME is the qty of movement, and Registrado is the date of the movement.
So, i need to create a column where if the sum of Ctd.UME (MB51) is equal to Ctd.Ped (SALES) and the Registrado date (MB51) is before or equal to Fe.Despacho (SALES) for the same Pedido, Pos, Material. if this is OK, must be "COMPLETED" if not "INCOMPLETED"
Please sorry for my english and i hope u can help me
Thanks
Solved! Go to Solution.
Hi,
I have tried both column and measure to reach your requirement:
By column:
Column =
VAR a =
CALCULATE (
SUM ( SALES[Ctd. Ped.] ),
FILTER (
ALLSELECTED ( SALES ),
SALES[Material] = EARLIER ( MB51[Material] )
&& SALES[Pedido] = EARLIER ( MB51[Ped.cite.] )
&& SALES[Pos.] = EARLIER ( MB51[PosPdCI] )
)
)
VAR b =
IF (
MB51[Ped.cite.] = RELATED ( SALES[Pedido] )
&& MB51[Material] = RELATED ( SALES[Material] )
&& MB51[PosPdCI] = RELATED ( SALES[Pos.] ),
IF (
MB51[Registrado] <= RELATED ( SALES[Fe. Despacho] ),
CALCULATE (
SUM ( MB51[Ctd.UME] ),
FILTER (
ALLSELECTED ( MB51 ),
MB51[Ped.cite.] = EARLIER ( MB51[Ped.cite.] )
&& MB51[Material] = EARLIER ( MB51[Material] )
&& MB51[PosPdCI] = EARLIER ( MB51[PosPdCI] )
)
),
-1
),
-1
)
RETURN
IF ( a = b, "COMPLETED", "INCOMPLETED" )
By measure:
Measure =
VAR a =
CALCULATE (
SUM ( SALES[Ctd. Ped.] ),
FILTER (
ALLSELECTED ( SALES ),
SALES[Material] IN FILTERS ( MB51[Material] )
&& SALES[Pedido] IN FILTERS ( MB51[Ped.cite.] )
&& SALES[Pos.] IN FILTERS ( MB51[PosPdCI] )
)
)
VAR b =
IF (
MAX ( MB51[Ped.cite.] ) IN FILTERS ( SALES[Pedido] )
&& MAX ( MB51[Material] ) IN FILTERS ( SALES[Material] )
&& MAX ( MB51[PosPdCI] ) IN FILTERS ( SALES[Pos.] ),
IF (
MAX ( MB51[Registrado] ) <= MAX ( SALES[Fe. Despacho] ),
CALCULATE (
SUM ( MB51[Ctd.UME] ),
FILTER (
ALLSELECTED ( MB51 ),
MB51[Ped.cite.] IN FILTERS ( MB51[Ped.cite.] )
&& MB51[Material] IN FILTERS ( MB51[Material] )
&& MB51[PosPdCI] IN FILTERS ( MB51[PosPdCI] )
)
),
-1
),
-1
)
RETURN
IF ( a = b, "COMPLETED", "INCOMPLETED" )
The result in my sample shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
Hi,
I have tried both column and measure to reach your requirement:
By column:
Column =
VAR a =
CALCULATE (
SUM ( SALES[Ctd. Ped.] ),
FILTER (
ALLSELECTED ( SALES ),
SALES[Material] = EARLIER ( MB51[Material] )
&& SALES[Pedido] = EARLIER ( MB51[Ped.cite.] )
&& SALES[Pos.] = EARLIER ( MB51[PosPdCI] )
)
)
VAR b =
IF (
MB51[Ped.cite.] = RELATED ( SALES[Pedido] )
&& MB51[Material] = RELATED ( SALES[Material] )
&& MB51[PosPdCI] = RELATED ( SALES[Pos.] ),
IF (
MB51[Registrado] <= RELATED ( SALES[Fe. Despacho] ),
CALCULATE (
SUM ( MB51[Ctd.UME] ),
FILTER (
ALLSELECTED ( MB51 ),
MB51[Ped.cite.] = EARLIER ( MB51[Ped.cite.] )
&& MB51[Material] = EARLIER ( MB51[Material] )
&& MB51[PosPdCI] = EARLIER ( MB51[PosPdCI] )
)
),
-1
),
-1
)
RETURN
IF ( a = b, "COMPLETED", "INCOMPLETED" )
By measure:
Measure =
VAR a =
CALCULATE (
SUM ( SALES[Ctd. Ped.] ),
FILTER (
ALLSELECTED ( SALES ),
SALES[Material] IN FILTERS ( MB51[Material] )
&& SALES[Pedido] IN FILTERS ( MB51[Ped.cite.] )
&& SALES[Pos.] IN FILTERS ( MB51[PosPdCI] )
)
)
VAR b =
IF (
MAX ( MB51[Ped.cite.] ) IN FILTERS ( SALES[Pedido] )
&& MAX ( MB51[Material] ) IN FILTERS ( SALES[Material] )
&& MAX ( MB51[PosPdCI] ) IN FILTERS ( SALES[Pos.] ),
IF (
MAX ( MB51[Registrado] ) <= MAX ( SALES[Fe. Despacho] ),
CALCULATE (
SUM ( MB51[Ctd.UME] ),
FILTER (
ALLSELECTED ( MB51 ),
MB51[Ped.cite.] IN FILTERS ( MB51[Ped.cite.] )
&& MB51[Material] IN FILTERS ( MB51[Material] )
&& MB51[PosPdCI] IN FILTERS ( MB51[PosPdCI] )
)
),
-1
),
-1
)
RETURN
IF ( a = b, "COMPLETED", "INCOMPLETED" )
The result in my sample shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |