Help with DAX

Hi christopherocc ,

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

1. Create a clustered column chart.
2. Use FacilityID or InsurerID (or any category column) for the Axis.
3. 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.

Hi christopherocc ,

Can you please give more context, i mean you can show the desired output and which several rows you want to display?

Hi christopherocc 

This is very similar to aging of receivables report I created.

https://community.fabric.microsoft.com/t5/Data-Stories-Gallery/Accounts-Receivable-Aging-Schedule/td-p/3960847 

There is a link to the tutorial on YouTube which has the sampe pbix in the description.