Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dimension column based on page date slicer value - possible?

I need to find out whether this is possible - we have a dimension table that has a column [First of Month] - which has data running from 01/01/2022,  01/02/2022,.....  up  to 01/05/2025.    If we b...
  • bhanu_gautam's avatar
    1 year ago

    Anonymous You can create a measure that calculates the preceding month based on the selected [First of Month] value from the slicer. 

    Create a measure for the selected [First of Month]:

    SelectedFirstOfMonth = SELECTEDVALUE('Calendar'[First of Month])

     

    Create a measure for the preceding [First of Month]:

    PrecedingFirstOfMonth = EOMONTH([SelectedFirstOfMonth], -1) + 1

     

    Create a measure to filter the data for the preceding month:

    DAX
    PrecedingMonthData =
    CALCULATE(
    SUM('YourTable'[YourDataColumn]),
    'Calendar'[First of Month] = [PrecedingFirstOfMonth]
    )

     

    Use the SelectedFirstOfMonth measure to filter the data for the selected month:

    DAX
    SelectedMonthData =
    CALCULATE(
    SUM('YourTable'[YourDataColumn]),
    'Calendar'[First of Month] = [SelectedFirstOfMonth]
    )

     

    For the first graph, use the SelectedMonthData measure.
    For the second graph, use the PrecedingMonthData measure.