Forum Discussion
Month by month comparison
HI,
I have a dataset which holds a ReportingDate (date), a SupplierSiteID (int) and a RiskFactor (float). Each SupplierSiteID will appear once per ReportingDate with an associated RiskFactor.
I want to allow users to select any 2 ReportingDates from the dataset and, for each SupplierSiteID, compare the RiskFactor for both of the users selected months.
This is obviously easily done if you are only going to compare to a set month but the users want the ability to compare the value for any 2 months.
I am not sure how to approach the issue so any advice would be welcomed
Thanks
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] ) )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
9 Replies
johnt75Super User
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] ) )Bearing in mind that I will need to display the RiskFactor for each SupplierSiteID for both of the months will a measure work? I though they would only hold single values.
Sorry, I have not worked much with measures so if I am asking a stupid question, please forgive me
- saritasw
Resolver II
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
- saritasw
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,
SaritaThank you for the reply. Will this approach allow me to display the results for every SupplierSiteID for the selected months? I need to display the difference in RiskFactor for every supplier for the selected months at the same time. SupplierSIteID can't be a slicer unfortunately, they want to see them all
