Issue with computing Prior Sum based on monthly data

[minghaoliang]

I have the following DAX codes:

function to calculate prior cost:

Prior Total Cost =
VAR Selected_Value_Current = calculate(firstnonblank(DateDim[Date],true()),
filter(DateDim,DateDim[Standard_MonthName]=selectedvalue(DateDim[Standard_MonthName])))

VAR Selected_Month_Previous = year(eomonth(Selected_Value_Date,-1))&"M"&format(month(eomonth(Selected_Value_Date,-1)),"00")
VAR _Prior_Total_Cost = calculate(sum('Customer Report'[EUR_LINE_TOTAL]),filter(all(DateDim),DateDim[Standard_MonthName]=Selected_Month_Previous))

return if(isblank(_Prior_Total_Cost),0 , _Prior_Total_Cost)

when I tried to run this query, it always return blank values for prior results: 

EVALUATE
VAR country = "NETHERLANDS"
VAR mth = "2025M01"
VAR customer = "Customer xx"

VAR table_sum = 
    SUMMARIZE(
        FILTER(
            'Customer Report',
            'Customer Report'[Country_Name] = country && 
            'Customer Report'[CONTRACTUAL_CUSTOMER] = customer && 'Customer Report'[Standard_MonthName] = mth
        ),
        'Customer Report'[Standard_MonthName], 
        DateDim[MonthIndex],
        'Customer Report'[New_Category], 
        'Customer Report'[Service_Group],
        "Current", [Current Total Cost],
        "Prior", [Prior Total Cost]
            )

[v-veshwara-msft]

Hi @minghaoliang ,

We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the "Accepted Solution" to assist others facing similar challenges.

If you still need assistance, please let us know.
Thank you.

[v-veshwara-msft]

Hi @minghaoliang ,
Just wanted to check if the solution provided has met your needs. If yes, Please consider marking it as "Accepted Solution" to assist others with similar queries. If further assistance is needed, please reach out.
Thank you.

[v-veshwara-msft]

Hi @minghaoliang ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft · Accepted solution]

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.

[AnalyticPulse]

hi @minghaoliang  
is the format of your 'DateDim'[Standard_MonthName] column same as YYYYMmm format? 

[minghaoliang]

Yes it is