Help with DAX

Super User

1 year ago

Please try the below steps:

1. Set Up the Date Slicer

Ensure you have a proper Date Table in your model, related to your data table (Master Payments). Use this Date Table for your slicer. For the slicer, set the field to DimDate[Date] (or equivalent) and enable the "Single select" option.

2. Create a Measure for the Most Recent Month

You need a measure that dynamically identifies the latest month based on the slicer selection:

Most Recent Month Filter = 
VAR SelectedDate = MAX(DimDate[Date]) -- Gets the latest date selected in slicer
VAR LatestMonth = MONTH(SelectedDate)
VAR LatestYear = YEAR(SelectedDate)
RETURN
    IF(
        ISBLANK(SelectedDate),
        BLANK(),
        CALCULATE(
            SUM('Master Payments'[Amount]),
            MONTH('Master Payments'[Month]) = LatestMonth &&
            YEAR('Master Payments'[Month]) = LatestYear
        )
    )

3. Create Measures for Each Column

Repeat similar logic for each column you want in the visual. For example:

Amount Owing:

Most Recent Amount Owing = 
VAR SelectedDate = MAX(DimDate[Date])
VAR LatestMonth = MONTH(SelectedDate)
VAR LatestYear = YEAR(SelectedDate)
RETURN
    CALCULATE(
        SUM('Master Payments'[Amount Owing]),
        MONTH('Master Payments'[Month]) = LatestMonth &&
        YEAR('Master Payments'[Month]) = LatestYear
    )

1-30 Days:

Most Recent 1-30 Days = 
VAR SelectedDate = MAX(DimDate[Date])
VAR LatestMonth = MONTH(SelectedDate)
VAR LatestYear = YEAR(SelectedDate)
RETURN
    CALCULATE(
        SUM('Master Payments'[1-30 Days]),
        MONTH('Master Payments'[Month]) = LatestMonth &&
        YEAR('Master Payments'[Month]) = LatestYear
    )

Repeat this logic for all the other columns: 120+ Days, 31-60 Days, 61-90 Days, 91-120 Days.

4. Add Measures to the Visual

Create a clustered column chart.
Use FacilityID or InsurerID (or any category column) for the Axis.
Add your measures (Most Recent Amount Owing, Most Recent 1-30 Days, etc.) to the Values field.

5. Optional: Adjust for Dynamic Titles

To make it clear which month the visual represents, create a dynamic title measure:

Dynamic Title = 
VAR SelectedDate = MAX(DimDate[Date])
RETURN
    "Data for " & FORMAT(SelectedDate, "MMMM YYYY")

Set this measure as the title of your visual (in the Format pane > Title > "fx" button).

6. Verify the Relationships

Ensure DimDate and Master Payments are properly related in your model, with DimDate as the one-side and Master Payments[Month] as the many-side.

With these measures, you can include several columns in your clustered column visual, all dynamically filtered by the most recent month selected in the slicer.

Please mark this as solution if it helps. Appreciate kudos.

Forum Discussion