Month End Data_% Change

[gmasta1129]

Hello, 

I am trying to calculate the % change month over month (month end/last day of month).  

**Please note the run date is first day of month because our data is on a 1 day lag.  Is it possible to create a fomula (calculated column or measure) to fix this? Where it looks at the date in '"Run Date" and pulls the date for the last day of the previous month? I couldnt figure out a formula for that. 

The two formula's i used are shown below.  It seems to be working but only for the last row (2/1/2022). The other two rows are 0.00% (see screenshot above).  Not sure what is wrong with my formulas.  

If there is an easier way to do this, please let me know. 

Utilization - Overall MOM % Chg = IFERROR((sum('daily_report'[Utilization Overall]) - [Utilization - Overall Prev Months])/[Utilization - Overall Prev Months],0)

Utilization - Overall Prev Months =
VAR __PREV_MONTH =
CALCULATE(
sum('daily_report'[Utilization Overall]),
DATEADD(daily_report[Run Date 2], -30, DAY)
) + CALCULATE(
sum('daily_report'[Utilization Overall]),
DATEADD(daily_report[Run Date 2], -31, DAY)
) + CALCULATE(
sum('daily_report'[Utilization Overall]),
DATEADD(daily_report[Run Date 2], -32, DAY)
) + CALCULATE(
sum('daily_report'[Utilization Overall]),
DATEADD(daily_report[Run Date 2], -29, DAY)
)
RETURN
__PREV_MONTH

[gmasta1129]

I have a slicer on the page which filters it by portfolio code ex: 54321, 12332, 23421 etc.  Does this need to be added to the lookup? And if so, how would that be done? 

[TomMartens]

Hey @gmasta1129 ,

please be aware that caculated columns will be only "calculated" during data refresh and design time.

Here you will find more information about the LOOKUPVALUE function: https://dax.guide/lookupvalue/

What it makes more difficult to provide further help is the fact that the data from your original post does not contain a column portfolio code.

Provide a Power BI file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and. measures). Upload the file onedrive or dropbox and share the link in this thread.

Regards,

Tom

[gmasta1129]

Hello Tom,

Thank you for the quick response.  

I copy and pasted the formula and receive the error message below...

"A table of mulitple values was supplied where a single value was expected." 

[TomMartens]

Hey @gmasta1129 ,

I assume you receive the error because lookupvalue finds more than matching row.
Either you add more than a single search/value pair to the LOOKUPVALUE function or you replace the function using FILTER in combination with an iterator function like SUMX to aggregate the values

If you need more help, create a pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link.

If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Regards,

Tom

[TomMartens · Accepted solution]

Hey @gmasta1129 ,

I have to admit that I do not fully understand why it's necessary to calculate the MoM change based on the Month's end date.
Nevertheless, my sample data looks like this:

The I use the below 2 DAX statements to create calculated columns, one returns the month end date of the current month based on Run Date 2 value and the other returns end of month of the previous month.

End Of Month = 
EOMONTH( 'Table'[Run Date 2] , 0 ) 

End Of Prev Month = 
EOMONTH( 'Table'[Run Date 2] , -1 ) 

Then my table looks like this:

Finally, I use the below DAX statement to create a calculated column that returns the MoM change:

MoM change = 
var prevMonthValue = LOOKUPVALUE('Table'[Utilization_Overlall] ,'Table'[End Of Month] , 'Table'[End Of Prev Month] )
return
if( isblank(prevMonthValue )
    , BLANK()
    , ( divide( 'Table'[Utilization_Overlall] , prevMonthValue ) - 1 ) * 100
)

I recommend, reading this article Time patterns – DAX Patterns, the article provides almost everything for date-based calculations, and also explains why using a dedicated calendar table is so important.

Regards,

Tom