Show data over time based on dynamic date selection

[gedal]

Hi 🙂

I want to make a report that shows how much a customer owes me in unpaid invoices over time. I should be able to change the date of interest in my analysis. So if I select a date in the past in the date filter, the measure I want to create should show me how much my customer owed me at that point in time.
I also want the amounts owed to be organized based on intervals of how many days the customer is overdue. The output should look like the following table:

Canvas column	Date selected	Customer ID	Current amount	1 -30 days	‘>30 days
Value type	Measure	Dimension	Measure	Measure	Measure
Description	The date selected in the date filter (can only select one and default to today() )	The unique customer number	To be paid but haven’t passed the due date for payment	Invoices that are not paid and was due 1-30 days ago	Invoices that are not paid and was due more than 30 days ago
Example	28.08.2023	1	15 000	20 000	0

I have a data model that includes a fact table of unpaid invoice amounts over time. The table has the following columns:

FACT Columns	Entry No_	Customer No_	Doc No_	Posting Date	Due Date	Amount	Calculated(days due)
Description	Unique	The customer No_	Not unique	Date of invoice	Date for the payment deadline	value	DATEDIFF( FACT[Due Date], TODAY(), DAY)
Example	1	1	1	07.08.2023	21.08.2023	20 0000	7
Example	2	1	2	14.08.2023	28.08.2023	10 000	0
Example	3	1	3	31.08.2023	14.09.2023	5 000	-15

The data model is as in the following diagram (note that the calendar dimension is detached from the model):

I have made the following measures to create the desired output:

Measure Name	Measure Dax code
[selected day]	var selectedDate = SELECTEDVALUE(DIM_calendar[DATE]) var switcher = SWITCH(true,     NOT(ISBLANK(selectedDate)),SELECTEDVALUE(DIM_calendar[DATE]),     ISBLANK(selectedDate),TODAY() ) return switcher
[Current amount]	var postingdate = SELECTEDVALUE(FACT[Posting Date]) var selectedday = [selected day] var dateselectionDiff = DATEDIFF(selectedday,TODAY(),DAY) var dynamicduedays = SUM(FACT[days due]) - dateselectionDiff var calc = CALCULATE(     SUM(FACT[Amount]),     KEEPFILTERS(FACT[days due] - dateselectionDiff <= 0),     KEEPFILTERS(FACT[Posting Date] >= selectedday) ) return calc
[1-30 days amount]	var dateselectionDiff = DATEDIFF([selected day],TODAY(),DAY) var selectedDay = [selected day] var calc = CALCULATE(     SUM(FACT[Amount]),     KEEPFILTERS(FACT[days due] - dateselectionDiff > 0),     KEEPFILTERS(FACT[days due] - dateselectionDiff <= 30),     KEEPFILTERS(FACT[Posting Date] <= selectedDay) ) return calc

PROBLEM:
When I select the date 28.08.23 in the calendar filter I do not get the output as expected, but the following:

Date selected	Customer ID	Current amount	1-30  days	‘>30 days
28.08.2023	1	5 000	20 000	0

Note that the value from entry No_ = 2 (10 000) is not included in the ‘Current amount’ Column. I would expect it to do so, since the ‘Current amount’ column should include ‘days due’ – ‘dateselectionDiff’ = 0.

If I change the selected date to one day later, the value from ‘Entry No_’ pops up in the ‘1-30 days’ column, as follows:

Date selected	Customer ID	Current amount	1-30  days	‘>30 days
29.08.2023	1	5 000	30 000	0

How can this be?

[amitchandak]

@gedal , You have create a measure like

Assume you are using date table joined to date of your table

Days =

var _max  = maxx(allselected(Date), Date[Date])

return

datediff(Max(Table[Due Date]), _max, day)

If need add filter Table Due > _max

Days =

var _max  = maxx(allselected(Date), Date[Date])

return

calculate(datediff(Max(Table[Due Date]), _max, day), filter(Table, Table[Due Date] >_max ))

Then you can use dynamic Segmentation for bucketing

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[gedal]

Thanks,
this was not what I was looking for in the specific question. But this was very useful for other features in the same report! 🙂