Forum Discussion

WickusB's avatar
WickusB
New Member
7 years ago
Solved

Summing data not in the slicer

Hi

 

I have a bank statement dashboard that shows various items for a selected month.

 

one of them being the total payments received for the month to date. 

 

I also want to add a total payments received for the month to date for the month before up to the same day.

 

i.e. if today is the 27th of June 2019 I already have a field that totals payments from 1st June to 27 June but now also want one that totals for 1 May to 27 May. In order to compare.

 

The problem is the data are getting filtered by the slicer to June 2019 so my dax formula for the month before gives me 0 which I assume is because I have June selected there is no May data

 

Regards,

Wickus

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi WickusB ,

    By my tests, you could create a calendar table with CALENDARAUTO() and do not create the relationship.

    Then please create the two measures with the formula below.

    selectmonth = 
VAR a =
    SELECTEDVALUE ( 'Table'[Month] )
RETURN
    CALCULATE (
        SUM ( Sheet21[sales] ),
        FILTER (
            'Sheet21',
            'Sheet21'[Date].[MonthNo] <= a
                && 'Sheet21'[Date] >= STARTOFMONTH ( 'Table'[Date] )
        )
    )
last month =
CALCULATE (
    SUM ( Sheet21[sales] ),
    FILTER (
        ALL ( 'Sheet21' ),
        'Sheet21'[Date].[MonthNo]
            = SELECTEDVALUE ( 'Table'[Month] ) - 1
            && 'Sheet21'[Date].[Day] <= DAY ( MAX ( 'Sheet21'[Date] ) )
    )
)

    Here is the output.

    For more details, please refer to my attachement.

    Best  Regards,

    Cherry

     

2 Replies

  • WickusB's avatar
    WickusB
    New Member
    7 years ago

    Hi

     

    I have a bank statement dashboard that shows various items for a selected month.

     

    one of them being the total payments received for the month to date. 

     

    I also want to add a total payments received for the month to date for the month before up to the same day.

     

    i.e. if today is the 27th of June 2019 I already have a field that totals payments from 1st June to 27 June but now also want one that totals for 1 May to 27 May. In order to compare.

     

    The problem is the data are getting filtered by the slicer to June 2019 so my dax formula for the month before gives me 0 which I assume is because I have June selected there is no May data

     

    Regards,

    Wickus

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi WickusB ,

    By my tests, you could create a calendar table with CALENDARAUTO() and do not create the relationship.

    Then please create the two measures with the formula below.

    selectmonth = 
VAR a =
    SELECTEDVALUE ( 'Table'[Month] )
RETURN
    CALCULATE (
        SUM ( Sheet21[sales] ),
        FILTER (
            'Sheet21',
            'Sheet21'[Date].[MonthNo] <= a
                && 'Sheet21'[Date] >= STARTOFMONTH ( 'Table'[Date] )
        )
    )
last month =
CALCULATE (
    SUM ( Sheet21[sales] ),
    FILTER (
        ALL ( 'Sheet21' ),
        'Sheet21'[Date].[MonthNo]
            = SELECTEDVALUE ( 'Table'[Month] ) - 1
            && 'Sheet21'[Date].[Day] <= DAY ( MAX ( 'Sheet21'[Date] ) )
    )
)

    Here is the output.

    For more details, please refer to my attachement.

    Best  Regards,

    Cherry