How calculate the previous month cost by YearMonth Text filed

[Syndicate_Admin]

Hi All,

I am trying to calculate the previous month cost by text YearMonth (Billing Month) of 'yyyymm' . (for example: 202301, 202303 ...)

As the YearMonth is Text type not a date, so I can not use the previousmonth method to directly calculate the previous month total cost. So I tried to change the format of YearMonth (Billing Month) to int, then change back to string.

----------------- Code As Below------------

previous month cost =

var selected_billing_month = SELECTEDVALUE('dim_Billing Month'[Billing Month])

var previous_month = CONVERT(selected_billing_month, INTEGER)-1

var format_previous_month = if(RIGHT(previous_month,2) = "00", BLANK(), if(previous_month <0, BLANK(), previous_month))

var string_format_previous_month = FORMAT(CONVERT(format_previous_month, STRING),"YYYYMM")

var filter_value = filter('dim_Billing Month', [Billing Month]=string_format_previous_month)

var previous_Month_Cost = CALCULATE(sum('fact_overview'[#cost]), filter('dim_Billing Month', [Billing Month]=string_format_previous_month))

return previous_Month_Cost

-------------- End Of Code -------------

I used above measure to get the total cost in a matrix table,  tried to replace 'string_format_previous_month' to '202301', the 202301 column could get the total cost although the other row can not get the total cost， I think the above code should be work, but I change it back to 'string_format_previous_month', the return value is nothing. 

Is there something I need change, or I should change the YearMonth field to Date type?

[Syndicate_Admin]

Thank you for the solution, will try and it seems work.

[Muhammad110]

It seems like the issue is with the format of the 'string_format_previous_month' variable. The FORMAT function returns a string, and the format string you provided ("YYYYMM") returns a string in the format of "202301", which is not a valid date format that can be recognized by Power BI.

Instead, you can try formatting the 'string_format_previous_month' variable as "YYYY/MM/01", which will return a string in the format of "2023/01/01". This format can be recognized as a valid date format by Power BI, and you can then use the DATEADD function to subtract one month and get the previous month's date.

Below is the modify code to achieve this:

previous month cost =
VAR selected_billing_month = SELECTEDVALUE('dim_Billing Month'[Billing Month])
VAR previous_month_date = DATEADD(DATEVALUE(FORMAT(selected_billing_month, "YYYY/MM/01")), -1, MONTH)
VAR previous_month = FORMAT(previous_month_date, "YYYYMM")
VAR previous_month_cost = CALCULATE(SUM('fact_overview'[#cost]), 'dim_Billing Month'[Billing Month] = previous_month)
RETURN previous_month_cost

In this code, we first format the 'selected_billing_month' variable as "YYYY/MM/01" to get a valid date format. We then use the DATEADD function to subtract one month from this date and get the previous month's date. Finally, we format this date as "YYYYMM" to get the previous month's value, and use it to filter the 'fact_overview' table to get the total cost for the previous month.

I hope this helps!

[v-jingzhang]

Hi 

If you must keep the YearMonth (Billing Month) column as Text type, I suggest that you add a new date-type column to the same table from YearMonth and use the new date-type column for calculation in the measure. This would make it easier to get the previous month and you don't have to convert the format in the measure. 

For example, you can add a month start date column with below DAX. Change it to Date type. 

Month Start = DATE(VALUE(LEFT('Table'[Billing Month],4)),VALUE(RIGHT('Table'[Billing Month],2)),1)

To get the previous month, you can use EDATE function easily. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.