DAX to get Monthly totals based on specific filters

[kumsha1]

Can someone help to check what's wrong with below DAX, it's stopped working recently where VAR SA is not returning any values now....

Our requirement is to get the monthly totals for the selected month, year and attribute, would greatly help if there's any other easy way to do this..TIA

MTDPlanValue (Monthly) =
VAR SM =
MONTH ( SELECTEDVALUE ( 'CALENDAR'[CALENDAR_DATE] ) )
VAR SY =
YEAR ( SELECTEDVALUE ( 'CALENDAR'[CALENDAR_DATE] ) )
VAR SA =
SELECTEDVALUE ( 'AttributeInfo'[Attribute] )
RETURN
CALCULATE (
SUM( 'Stg_PPD DailyReporting_Live_V'[DAILY_PLAN_VALUE] ),
FILTER (
ALL ( 'CALENDAR' ),
'CALENDAR'[CALENDAR_MONTH] = SM
&& 'CALENDAR'[CALENDAR_YEAR] = SY
),
FILTER (
ALL ( 'AttributeInfo' ),
'AttributeInfo'[Attribute] = SA
)
)

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but I assume there are some cases end users select more than two attributes.

If end users keep selecting only one year and only one month, but selecting more than two attributes, and if this is considered as a correct behavior, please try the below.

MTDPlanValue (Monthly) =
VAR SM =
    MONTH ( SELECTEDVALUE ( 'CALENDAR'[CALENDAR_DATE] ) )
VAR SY =
    YEAR ( SELECTEDVALUE ( 'CALENDAR'[CALENDAR_DATE] ) )
VAR SA =
    VALUES ( 'AttributeInfo'[Attribute] )
RETURN
    CALCULATE (
        SUM ( 'Stg_PPD DailyReporting_Live_V'[DAILY_PLAN_VALUE] ),
        FILTER (
            ALL ( 'CALENDAR' ),
            'CALENDAR'[CALENDAR_MONTH] = SM
                && 'CALENDAR'[CALENDAR_YEAR] = SY
        ),
        FILTER ( ALL ( 'AttributeInfo' ), 'AttributeInfo'[Attribute] IN SA )
    )

[kumsha1]

@Jihwan_Kim , users will select one year/month and attribute only...i have tried below simple DAX and it's not returning any value, what could be wrong here, any ideas.. TIA

MTDPlanValue (Monthly) =

VAR SA =

    SELECTEDVALUE ( 'AttributeInfo'[Attribute] )

RETURN

    SA

[Jihwan_Kim]

Hi,

It is difficult for me to tell what is going on without seeing your pbix file.

Please try the below measure whether it is returning one value or not. 

Checking: =
VAR SA =
    VALUES ( 'AttributeInfo'[Attribute] )
RETURN
    CONCATENATEX ( SA, 'AttributeInfo'[Attribute], ", " )

[Ashish_Mathur]

Hi,

If your Year and Month slicer are from the Calendar table, then this simple measure should work

=SUM( 'Stg_PPD DailyReporting_Live_V'[DAILY_PLAN_VALUE] )

Hope this helps.

[kumsha1]

@Ashish_Mathur , this measure should always give the Monthly total (for selected month, year and attribute) not the day total.

[Ashish_Mathur]

I am still very confused about what you want.  Try this measure

Measure = calculate(SUM( 'Stg_PPD DailyReporting_Live_V'[DAILY_PLAN_VALUE] ),datesbetween(calendar[date],eomoth(min(calendar[date]),-1)+1,eomoth(min(calendar[date]),0))