Getting Duplicate Value while using EARLIER function in Calculated column

[Arun---2308]

When I use the below formula to create a Calculated Column in Power BI Desktop , for some of the rows  i get correct number and for remaining the value is double to that of the correct number. Please let me know how the formula could be corrected. Below is the formula.                                                                    Monthwise Ship qty = SUMX(FILTER('Shipment Short Forecast','Shipment Short Forecast'[Month]= EARLIER('Shipment Short Forecast'[Month]) && 'Shipment Short Forecast'[File_Month]= EARLIER('Shipment Short Forecast'[File_Month] )),'Shipment Short Forecast'[Ship_Qty_Forecasted])

[Bibiano_Geraldo]

Hi @Arun---2308 ,

Please try the bellow DAX:

Ship_Qty_Forecasted_Column = 
CALCULATE(
    SUM('Table'[Ship_Qty]),
    ALLEXCEPT('Table', 'Table'[Month], 'Table'[File_Month])
)

This will result in this:

[Arun---2308]

Take the below table   an example how the actual data appears. 

Supplier	Keyy	Month	File_Month	Ship_Qty
ABC	A1	May-24	31-May-24	100
BCD	B2	May-24	31-May-24	150
EFG	C3	May-24	30-May-24	200

When the query runs for a row where Month = May-24 and File_Month = 31-May-24, the total Ship_Qty_Forecasted for that combination will be 250 (100 + 150), because we’re summing up all the rows with the same Month and File_Month. So in the calculated column which i trying to calculate the output should be 250 for all the rows with the same Month and File_Month.

[Anonymous]

Hi @Arun---2308 ,

Please try:

Monthwise Ship qty = 
CALCULATE (
    SUM ( 'Shipment Short Forecast'[Ship_Qty_Forecasted] ),
    ALLEXCEPT (
        'Shipment Short Forecast',
        'Shipment Short Forecast'[Month],
        'Shipment Short Forecast'[File_Month]
    )
)

Or:

Monthwise Ship qty = 
VAR __curr_month = 'Shipment Short Forecast'[Month]
VAR __curr_file_month = 'Shipment Short Forecast'[File_Month]
VAR __result =
    CALCULATE (
        SUM ( 'Shipment Short Forecast'[Ship_Qty_Forecasted] ),
        FILTER (
            ALL ( 'Shipment Short Forecast' ),
            'Shipment Short Forecast'[Month] = __curr_month
                && 'Shipment Short Forecast'[File_Month] = __curr_file_month
        )
    )
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Arun---2308]

I tried  both the formula but just like the earlier result for few line items i will get the correct number but for rest the number which i get is double of the correct number. Like for instance if 1000 is the total Ship qty than for some of the line items i get the result as 2000. Any idea if there would be any other reason which i am not getting the correct numbers.?

[rajendraongole1]

Hi @Arun---2308 - Can you please share with some sample data as input and expected output? so that it helps to analyze and share insights. 

Thank you.