Compare monthly amounts to the first month selected

[daviddavidson]

 have years of data for telecom charges with an accounting date.

The Month to Month quick measure is almost what I want, but not quite.  I have modified it below to return the $ value instead of the percentage.  The problem is the Previous month is always -1 and I either need it to be fixed on the base month. or increment, like -1 then -2 then -3 etc.

For example if I choose all of 2021 I want the previous month calculation to always be January while the current month cycles through all of the months.

Also the range will be user selected so the first month could be any month and the duration could be any length.

Any ideas?

Savings =

IF(

    ISFILTERED('Telecom wireline all years'[Accounting Period]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH =

        CALCULATE(

            SUM('Telecom wireline all years'[Distribution Amount]),

            DATEADD('Telecom wireline all years'[Accounting Period].[Date], -1, MONTH

            )

        )

    RETURN

            SUM('Telecom wireline all years'[Distribution Amount]) - __PREV_MONTH

)

[amitchandak · Accepted solution]

@daviddavidson , Try measure like

First Month of Year =
var _max1 = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1*month(_max1) )+1

var _max = eomonth(_min,0)
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

[v-yalanwu-msft · Accepted solution]

Hi,  @daviddavidson ;

Try it.

Measure = 
var _min= CALCULATE(MIN('Telecom wireline all years'[Accounting Period]),ALLSELECTED('Telecom wireline all years'))
return 
SUM([Distribution Amount])-
CALCULATE(SUM('Telecom wireline all years'[Distribution Amount]),FILTER(ALL('Telecom wireline all years'),EOMONTH([Accounting Period],0)=EOMONTH(_min,0)))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daviddavidson]

Yalan Wu,

Thanks this did most of what I needed.  I used ALLEXCEPT instead of ALL to keep some of the filters.  I appreciate the help.

[amitchandak · Accepted solution]

@daviddavidson , Try measure like

First Month of Year =
var _max1 = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1*month(_max1) )+1

var _max = eomonth(_min,0)
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79