Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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] ) )
---------------------------------------------------
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |