Month by month comparison

[Matt_dev]

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

[johnt75 · Accepted solution]

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] )
)

[saritasw · Accepted solution]

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 Dates

SelectedDate1 = MIN('DateSelection'[ReportingDate]) -- for slicer 1
SelectedDate2 = MAX('DateSelection'[ReportingDate]) -- for slicer 2

3. 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]

Thank 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 

[saritasw]

@Matt_dev ,
If you place the measures (like RiskFactor_Date1, RiskFactor_Date2, and the difference) in a table / matrix visual that includes SupplierSiteID. 
All in one table — no slicer for SupplierSiteID needed.🙂

[johnt75 · Accepted solution]

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] )
)

[Matt_dev]

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

[johnt75]

They are 2 separate measures, one for each month. You can add them both to a table or matrix visual for each site.

[Matt_dev]

Thank you very much 

[saritasw]

@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

[Matt_dev]

I will try this out right away. Thank you ver much @saritasw and @johnt75