Issue with computing Prior Sum based on monthly data

Hi minghaoliang ,
Thanks for posting in Microsoft Fabric Community,

Based on your DAX code and the behavior you described, the reasons the Prior Total Cost measure returns blank values could be:

>>In your code, you declared Selected_Value_Current but later referenced Selected_Value_Date, which is undefined. This would cause the prior month calculation to fail.

>>Using ALL(DateDim) inside the FILTER will not remove any filters already applied on the 'Customer Report' table. Since your query already filters on 'Customer Report'[Standard_MonthName], the calculation cannot correctly find the previous month's data.

>>A mismatch in date handling--FIRSTNONBLANK(DateDim[Date], TRUE()) returns a date, but the logic depends on correctly converting it back to your "YYYYMmm" format for the lookup.

Also, please make sure:

   i. 'Customer Report'[Standard_MonthName] values match exactly with 'DateDim'[Standard_MonthName]      format (like 2025M01).

   ii. There is actual data available for the previous month you are querying (for example, if querying 2025M01, there should be 2024M12 data available).

Here is a corrected version of the Prior Total Cost measure you can try:

Prior Total Cost =
VAR SelectedDate = 
    CALCULATE(
        FIRSTNONBLANK(DateDim[Date], 1),
        FILTER(
            DateDim,
            DateDim[Standard_MonthName] = SELECTEDVALUE(DateDim[Standard_MonthName])
        )
    )

VAR PreviousMonthCode = 
    YEAR(EOMONTH(SelectedDate, -1)) & "M" & FORMAT(MONTH(EOMONTH(SelectedDate, -1)), "00")

VAR PriorCost = 
    CALCULATE(
        SUM('Customer Report'[EUR_LINE_TOTAL]),
        FILTER(
            ALL('Customer Report'),
            'Customer Report'[Standard_MonthName] = PreviousMonthCode
        )
    )

RETURN
    IF(ISBLANK(PriorCost), 0, PriorCost)

This version correctly fetches the prior month's total by properly constructing the previous month code and applying the filter on 'Customer Report'.

Let us know if this helps or if you have any further questions.

If this post helps, then please consider to Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best regards.

Vinay.