two months variance based on month selection in slicer

[PuneetSoni]

Can someone please help, i wanted to bring in another column which will show variance between two months selected from slicer.

[PuneetSoni]

https://rcirogers-my.sharepoint.com/:x:/r/personal/puneet_soni_rci_rogers_ca/Documents/Power%20BI/Sa... 

[v-yiruan-msft]

Hi @PuneetSoni ,

Thanks for your feedback. Please try to delete the relationship between your fact table 'COMM_ANALYSIS' and Calendar table. Then check if you can get the desired result by the previous measures... Could you please grant me the proper permission to it since I can't access your shared file? Thank you.

Max Month Value =
VAR _year =
SELECTEDVALUE ( 'Calendar'[Year] )
VAR _maxmonth =
MAX ( 'Calendar'[Month] )
RETURN
CALCULATE (
[Avg. Comm],
FILTER (
'Table',
YEAR ( 'COMM_ANALYSIS'[Date] ) = _year
&& MONTH ( 'COMM_ANALYSIS'[Date] ) = _maxmonth
),
ALL ( 'Calendar' )
)

2.
Min Month Value =
VAR _year =
SELECTEDVALUE ( 'Calendar'[Year] )
VAR _minmonth =
MIN ( 'Calendar'[Month] )
RETURN
CALCULATE (
[Avg. Comm],
FILTER (
'Table',
YEAR ( 'COMM_ANALYSIS'[Date] ) = _year
&& MONTH ( 'COMM_ANALYSIS'[Date] ) = _minmonth
),
ALL ( 'Calendar' )
)

3.
Variance = [Max Month Value]-[Min Month Value]

---

Best Regards

[PuneetSoni]

Thank you Rena. Appreciate your help. I am still not getting the desired results. Changed the Max Month and Min Month value but it is giving variance for same month. I am attaching a sample xl sheet data. All i want is i can select any two months from slicer and get the row wise variance for that.

I have two tables with below relationships

Also here are the three measures

1.
Max Month Value =
VAR _year =
SELECTEDVALUE ( 'Calendar'[Year] )
VAR _maxmonth =
MAX ( 'Calendar'[Month] )
RETURN
CALCULATE (
[Avg. Comm],
FILTER (
'Table',
YEAR ( 'Table'[Date] ) = _year
&& MONTH ( 'Table'[Date] ) = _maxmonth
),
ALL ( 'Calendar' )
)

2.
Min Month Value =
VAR _year =
SELECTEDVALUE ( 'Calendar'[Year] )
VAR _minmonth =
MIN ( 'Calendar'[Month] )
RETURN
CALCULATE (
[Avg. Comm],
FILTER (
'Table',
YEAR ( 'Table'[Date] ) = _year
&& MONTH ( 'Table'[Date] ) = _minmonth
),
ALL ( 'Calendar' )
)

3.
Variance = [Max Month Value]-[Min Month Value]

[PuneetSoni]

Thank you both, i tried but i am getting same values in both measures. However, i should get previous month in Min Month value when selected from slicer. Here are my three measures-

1. 

Max Month Value =

var _max = MAXX(allselected('Calendar'[Month]), 'Calendar'[Month])

var _min = EOMONTH(_max, -1)

return

calculate([Avg. Comm], Filter('Calendar','Calendar'[Month] <=_max && 'Calendar'[Month] >=_min) )

2.

Min Month Value =

var _min = Minx(allselected('Calendar'[Month]), 'Calendar'[Month])

var _max = EOMONTH ( _min, 0)-- Here i tried -1 also but then Min Month value retuns is blank

return

calculate([Avg. Comm], Filter('Calendar','Calendar'[Month] <=_max && 'Calendar'[Month] >=_min) )

3.

Variance = [Max Month Value]-[Min Month Value]

[v-yiruan-msft]

Hi @PuneetSoni ，

According to your screenshot, it only select one option for Month slicer. Then Max Month Value and Min Month Value are the same. The variance will be 0. Do you want to get the variance when select the different two months in Month slicer? If yes, you can change the formula of your measures as below and check if you can get the expected result...

Max Month Value =
VAR _year =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR _maxmonth =
    MAX ( 'Calendar'[Month] )
RETURN
    CALCULATE (
        [Avg. Comm],
        FILTER (
            'Table',
            YEAR ( 'Table'[Date] ) = _year
                && MONTH ( 'Table'[Date] ) = _maxmonth
        ),
        ALL ( 'Calendar' )
    )

Min Month Value =
VAR _year =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR _minmonth =
    MIN ( 'Calendar'[Month] )
RETURN
    CALCULATE (
        [Avg. Comm],
        FILTER (
            'Table',
            YEAR ( 'Table'[Date] ) = _year
                && MONTH ( 'Table'[Date] ) = _minmonth
        ),
        ALL ( 'Calendar' )
    )

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[PuneetSoni]

Hi Amit,

Thanks for the quick reply. Yes i am using date calendar in my slicer. but it's giving me error in the date syntax in return section

[amitchandak]

@PuneetSoni , Hope you are using Date table in the slicer

You need measures like

Max Month Value =
var _max = Maxx(allselected(Date), Date[Date])
var _min = eomonoth(_max, -1)+1

return
calculate(Sum(Table[Value]), Filter(Date,Date[Date] <=_max && Date[Date] >=_min) )

Min Month Value =
var _min = Minx(allselected(Date), Date[Date])
var _max = eomonoth(_max, 0)

return
calculate(Sum(Table[Value]), Filter(Date,Date[Date] <=_max && Date[Date] >=_min) )

Varinace = [Max Month Value] - [Min Month Value]

[bchager6]

@amitchandakThis looks pretty slick! I have a few questions if you wouldn't mind:

What's the reason for adding +1 below?

Max Month Value =
var _max = Maxx(allselected(Date), Date[Date])
var _min = eomonth(_max, -1)+1

return
calculate(Sum(Table[Value]), Filter(Date,Date[Date] <=_max && Date[Date] >=_min) )

Should _max be _min?

Min Month Value =
var _min = Minx(allselected(Date), Date[Date])
var _max = eomonth(_max, 0)