How to find difference of total count based on every month

[rehman1]

Hello All, i am trying to find difference in total difference in the count of members each month. The goal is to use two slicer visuals for Months and a card visual which will show the difference of total count of member each month. so lets say that a user select January from one Slicer and February from another then the card visual should show the difference of total count. it could be either a positive value or negative.

any help or suggestions are appreciated.

Thanks in advance

[v-frfei-msft · Accepted solution]

Hi @rehman1 ,

One sample for your reference. Please check the following steps as below.

1. Create two calculated tables as below.

date1 = ADDCOLUMNS(CALENDARAUTO(),"ym",FORMAT([Date],"yyyymmmm"))

date2 = ADDCOLUMNS(CALENDARAUTO(),"ym2",FORMAT([Date],"yyyymmmm"))

2. To create a measure to get the excepted result.

Measure = 
VAR s1 =
    SELECTEDVALUE ( date1[ym] )
VAR s2 =
    SELECTEDVALUE ( date2[ym2] )
VAR a =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'Table', FORMAT ( 'Table'[date], "yyyymmmm" ) = s1 )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'Table', FORMAT ( 'Table'[date], "yyyymmmm" ) = s2 )
    )
RETURN
    a - b

[Ashish_Mathur]

Hi,

Why do you need 2 slicers for months?  Just one slicer should do in which you can select a date range.

[MattAllington]

You need 2 calendar tables. It’s probabaly easiest to have the second with an inactive relationship, then use USERELATIONSHIP and ALL

the first calendar table should be easy. 

The second with an Inactive relationship shouldn’t be something like

=CALCULATE(COUNTROWS(data),USERELATIONSHIP(Calendar2[date],data[date]),ALL(Calendar))

read more at the bottom of this article here https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/