Measure - current and past month totals based on slicer selection

[jcollins_IFM]

Hoping this isn't too basic a question, but after a couple of days of unsuccessfully trying to modify solutions to 'similar' situations online I thought it might be time to ask the question directly. 

I am trying to calculate current and past month values, based on the year and month the user has selected in a slicer, the slicer values are based on the Month and Year values from 'ReportingPeriod' column in the sample below

SourceID	ReportingPeriod
S1	1/12/2020
S2	1/12/2020
S3	1/12/2020
S4	1/12/2020
S5	1/12/2020
S6	1/12/2020
S1	1/01/2021
S2	1/01/2021
S1	1/01/2021
S3	1/01/2021
S4	1/01/2021
S1	1/02/2021
S2	1/02/2021
S3	1/02/2021
S4	1/02/2021
S5	1/02/2021

I would like to create a couple of measures which will show a distinct count of the 'sourceID' values for both the current month and the previous month based on the month and year selected in the slicer. 

If the user selected February 2021 in the slicer, then the value for the current month should be 5 and the value for the previous month should be 4. If the user selected January 2021 in the slicer, then the value for the current month should be 4 and the value for the previous month should be 6. 

The main issue I'm having is that the slicer appears to be filtering out all data aside from the selected month / year, so when I try to create a 'Previous Year' calculation it comes up blank. I've removed the interaction between the slicer and the visual I want to use for Prev Month but now the criteria is too loose, using the example above the previous month calculation returns 6 regardless of the slicer selection because that's the total number of distinct values in the sourceID column across all reporting dates. 

Hopefully that's enough information to get started, but if more is required I'll be happy to provide...

Regards, 

Jason

[v-yetao1-msft · Accepted solution]

Hi @jcollins_IFM 

You can create a calendar date table as a slicer to filter the data in your data table .

(1)Create a calendar date table and a column with “Year-Month” format .

Calendar Date = CALENDAR(DATE(2020,01,01),DATE(2021,12,31))

Year Month = YEAR('Calendar Date'[Date]) & "-" & MONTH('Calendar Date'[Date])

(2)Create a one to many relationship between calendar table and data table .

(3)Create a slicer with the field 'Calendar Date'[Year Month] to filter data .

(4)Create measures to count the numbers of 'Table'[SourceID] .

current month count = DISTINCTCOUNT('Table'[SourceID])

previous month count = CALCULATE(DISTINCTCOUNT('Table'[SourceID]),PREVIOUSMONTH('Calendar Date'[Date]))

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@jcollins_IFM,

This solution requires a date table that has a relationship with the data table. Be sure to use DimDate[Month Year] in the date slicer. Time intelligence calculations work best with a date table.

Measures:

Count SourceID = DISTINCTCOUNT ( Table1[SourceID] )

Count SourceID Previous Month = CALCULATE ( [Count SourceID], PREVIOUSMONTH(DimDate[Date] ) )

---------------------------------------------------