topic Re: Calculate last 12 months data and MOM% in Power Bi in DAX Commands and Tips

Calculate last 12 months data and MOM% in Power Bi

Jack_Fan2309 — Fri, 17 Mar 2023 03:01:31 GMT

Hi,

I wanna display Last 12 months sales data and those MOM% and using an other slicer for time selection.
Here is what I'd like to show in Power BI:

I am able to achieve Last 12 months sales by using "DATESINPERIOD" function but now I am struggling with those MOM%. The DAX I used as follows.

Any help is appreciated
Regards,

Jack

Last 12 month MOM% =
VAR _Currentdate =
DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ( 'Date'[Month] ), 1 )
VAR _Last12month =
CALCULATE (
SUM ( 'Sales'[Actual] ),
DATESINPERIOD ( 'Date'[Date], _Currentdate, - 12, MONTH )
)
VAR _PREV_MONTH =
CALCULATE ( _Last12month, DATEADD ( 'Date'[Date], -1, MONTH ) )
RETURN
_PREV_MONTH

Re: Calculate last 12 months data and MOM% in Power Bi

Jihwan_Kim — Fri, 17 Mar 2023 03:30:50 GMT

Hi,

I am not sure if I understood your question correctly, but please try something like below whether it works in your datamodel.

Last 12 month MOM% fix = VAR _Currentdatemonthstart = DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ( 'Date'[Month] ), 1 ) VAR _Currentdateprevmonthstart = IF ( SELECTEDVALUE ( 'Date'[Month] ) <> 1, DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ( 'Date'[Month] ) - 1, 1 ), DATE ( SELECTEDVALUE ( 'Date'[Year] ) - 1, 12, 1 ) ) VAR _Last12month = CALCULATE ( SUM ( 'Sales'[Actual] ), DATESINPERIOD ( 'Date'[Date], _Currentdatemonthstart, - 12, MONTH ) ) VAR _PREV_MONTH_12month = CALCULATE ( SUM ( 'Sales'[Actual] ), DATESINPERIOD ( 'Date'[Date], _Currentdateprevmonthstart, - 12, MONTH ) ) RETURN DIVIDE ( _Last12month - _PREV_MONTH_12month, _PREV_MONTH_12month )

Re: Calculate last 12 months data and MOM% in Power Bi

Jack_Fan2309 — Fri, 17 Mar 2023 04:54:55 GMT

Dear Kim,

Thanks so much for your reply. I have just tried with your suggestion but the results as the following photo. Maybe I haven't solved my problem yet.

I have used three Measures as follows:

1. Monthly Sales =
VAR _Currentdatemonthstart = DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ('Date'[Month] ), 1 )
VAR _Last12month = CALCULATE (SUM ( 'Sales'[Actual] ),DATESINPERIOD ( 'Date'[Date], _Currentdatemonthstart, - 12, MONTH ))
RETURN
_Last12month

2. Previous Month Sales =
VAR _Currentdateprevmonthstart = IF (SELECTEDVALUE ( 'Date'[Month] ) <> 1,
DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ( 'Date'[Month] ) - 1, 1 ),
DATE ( SELECTEDVALUE ( 'Date'[Year] ) - 1, 12, 1 ) )
VAR _PREV_MONTH_12month = CALCULATE (SUM ( 'Sales'[Actual] ), DATESINPERIOD ( 'Date'[Date], _Currentdateprevmonthstart, - 12, MONTH ))
RETURN
_PREV_MONTH_12month

3. MOM% : The DAX is as same as you suggested.

Thanks again.

Best Regards,

Jack