Help with DAX

[christopherocc]

Hello.

I have this table:

FacilityID	Month	InsurerID	Amount Owing	1-30 Days	120+ Days	31-60 Days	61-90 Days	91-120 Days
SS00001	3/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	4/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	5/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	6/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	1/1/2024 0:00	10005	$999	999	999	999	999	999
SS00001	2/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	5/1/2024 0:00	10005	$999	999	999	999	999	999
SS00001	7/1/2024 0:00	10005	$999	999	999	999	999	999
SS00001	11/1/2024 0:00	10005	$999	999	999	999	999	999
SS00001	6/1/2024 0:00	10005	$999	999	999	999	999	999
SS00001	7/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	8/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	11/1/2024 0:00	10006	$999	999	999	999	999	999
SS00001	2/1/2024 0:00	10005	$999	999	999	999	999	999
SS00001	8/1/2024 0:00	10005	$999	999	999	999	999	999

I would like create a clustered column visual with the data except I want the data to be filtered by the most recent month that is selected in a date slicer. I also want to include several rows. 

I was able to acheive something similar albeit only with one column. :

Most Recent Month Payment Amount = 
VAR LatestMonth = MONTH([LatestDate])
VAR LatestYear = YEAR([LatestDate])
VAR PaymentAmount =
    CALCULATE(
        SUM('Master Payments'[Amount]),
        MONTH(DimDate[Date]) = LatestMonth && YEAR(DimDate[Date]) = LatestYear
    )
RETURN 
    IF(
        ISBLANK(PaymentAmount),
        "None",
        PaymentAmount
    )

The difference would be that I want to include several columns rather than just one I used in my previous example.

Is this feasable? 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[danextian]

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

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

[Bibiano_Geraldo]

Hi @christopherocc ,

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

[FarhanJeelani]

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.