Date slicer and matrix values

[Anonymous]

Hello! I need to filter a dueamount in my matrix and it is not working with the between date slicer (PostingDate). for example, my matrix's columns are buckets, so ''0-29days'', ''30-60days'', ''61-90days'', ''91 and over''. these are the number of days between the duedate and the posting date in my case. I have 2500$ dueamount with duedate (2022-03-15) and posting date (2022-03-19) so that's 4daysdue. My 2500$ does not show in ''0-29days'' column.... Thanks!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Due to I don't know your data model, here I create a sample to have a test.

Buckets = 
DATATABLE("Buckets",STRING,
"Index",INTEGER,
{
    {"0-29days",1},
    {"30-60days",2},
    {"61-90days",3},
    {"91 and over",4}
}
)

Calendar = CALENDARAUTO()

Measure:

Measure = 
VAR _SELECTDATE = SELECTEDVALUE('Calendar'[Date])
VAR _DATEDIFF = DATEDIFF(MAX('Table'[duedate]),_SELECTDATE,DAY)
RETURN
SWITCH(MAX(Buckets[Buckets]),"0-29days",IF(_DATEDIFF<=29,1),"30-60days",IF(_DATEDIFF<=60&&_DATEDIFF>29,1),"61-90days",IF(_DATEDIFF<=90&&_DATEDIFF>60,1),IF(_DATEDIFF>90,1))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.