Help Needed: Filtering Data

[NickProp28]

Hey everyone!

I’ve got a simple question, but it’s really been bugging me. I have two tables: dimData and Calendar, linked by Calendar[date] to dimData[InvoiceDate].

What I need is that when I select (like 'Nov 2023') from my 'Month Year' slicer, the table should only show records where the InvoiceDate is earlier or equal than 'Nov 2023' and the FullyPaidDate is later than 'Nov 2023'.

I’ve tried a bunch of different approaches, but I still can’t get it to work. If anyone could help, I’d really appreciate it!

Attached with pbix: https://drive.google.com/file/d/1RkLd58vqie6515hOJOYUe0Hn51qdyj1x/view?usp=sharing

The expectation result should be like this: 

[danextian · Accepted solution]

Hi @NickProp28 

Use a disconnected dates table as using the one related to your fact will filter the records to just that specific month although the total is what's expected.  Please see the attached pbix.

[_AAndrade]

Hi @NickProp28,

I disable the relationship and wrote this measure:

Amount Context = 
 VAR _InvoiceDate = ENDOFMONTH('Calendar'[Date])
 VAR _PaidDate = _InvoiceDate + 1
 VAR _TotalMeasure = 
  SUMX(
        FILTER(
            ALL(dimData),
            dimData[InvoiceDate] <=_InvoiceDate && dimData[FullyPaidDate] >=_PaidDate
        ),
        dimData[Sum of InvertedAmount]
  )
    
 VAR _RowMeasure = 
    IF(
        MIN(dimData[InvoiceDate]) <=_InvoiceDate && MIN(dimData[FullyPaidDate]) >=_PaidDate, 
        SUM(dimData[Sum of InvertedAmount]),
        BLANK()
    )
RETURN
    IF(
        ISFILTERED(dimData[Company]),
        _RowMeasure,
        _TotalMeasure
        )

The final result was this:

I'm attaching a pbix file with my solution.

If you need to have a relationship between calendar tabel and dimData you could create another calendar table and use it to use as filter and use the same measure provided.