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
Matt_dev ,
You're right — DAX measures return a single value, not rows. But in Power BI, measures adjust based on where you use them.
If you place a measure in a table or matrix with SupplierSiteID, it will calculate separately for each row. So it works per SupplierSiteID, thanks to how the visual provides context.
So yes, measures will work as long as -
You're using them in a visual with SupplierSiteID (like a table or matrix)
You're filtering or calculating based on the selected ReportingDate from your slicers