Forum Discussion
Month by month comparison
- 1 year ago
Create 2 date tables, one for each slicer. Do not link these tables to your main fact table. You can then produce measures like
Month 1 Value = CALCULATE ( SUM ( 'Table'[Risk Factor] ), TREATAS ( VALUES ( 'Date Slicer 1'[Date] ), 'Table'[Reporting Date] ) ) Month 2 Value = CALCULATE ( SUM ( 'Table'[Risk Factor] ), TREATAS ( VALUES ( 'Date Slicer 2'[Date] ), 'Table'[Reporting Date] ) ) - 1 year ago
To allow users to select any two ReportingDates and compare the RiskFactor for each SupplierSiteID, the best approach is to use two disconnected date slicers combined with DAX measures.
1. Use following DAX for both date slicers -DateSelection = DISTINCT(SELECTCOLUMNS('YourData', "ReportingDate", 'YourData'[ReportingDate]))OR
DateSelection = CALENDAR(MIN('YourData'[ReportingDate]), MAX('YourData'[ReportingDate]))Use the DateSelection[ReportingDate] field twice - once for each slicer. Label them “Date 1” and “Date 2” to make the user selection intuitive.
2. Create Measures for Selected DatesSelectedDate1 = MIN('DateSelection'[ReportingDate]) -- for slicer 1 SelectedDate2 = MAX('DateSelection'[ReportingDate]) -- for slicer 23. Create Measures to Fetch the RiskFactor
RiskFactor_Date1 = CALCULATE( MAX('YourData'[RiskFactor]), 'YourData'[ReportingDate] = [SelectedDate1] ) RiskFactor_Date2 = CALCULATE( MAX('YourData'[RiskFactor]), 'YourData'[ReportingDate] = [SelectedDate2] )4. Compare the Values
RiskFactor_Diff = [RiskFactor_Date2] - [RiskFactor_Date1]You may wanna use percentage here to show your result.
***********************************************************************************************************************If this solution worked for you, kindly mark it as Accept as Solution. This would be helpful for other members who may encounter similar issues and feel free to give a Kudos, it would be much appreciated!
Thank you,
Sarita