Forum Discussion

Matt_dev's avatar
Matt_dev
Frequent Visitor
1 year ago
Solved

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.   ...
  • johnt75's avatar
    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] )
    )
    
  • saritasw's avatar
    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 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