I need some help on this specific point (perhaps I miss a basic).
I want to create a page with an aged balance with the capability to have a past view if I need, using a filter date to select the past date (if not, using TODAY() ) and use it into my DATEDIFF formula.
To do this, I created a Table "Calendar input" which fit with my data table :
Calendar input =
ADDCOLUMNS (
CALENDAR (
EOMONTH ( MIN ( 'Vendor Ledger Entry'[Posting Date] ),-1),
EOMONTH ( MAX ( 'Vendor Ledger Entry'[Posting Date] ),+0)
),
"Mois", FORMAT ( [Date], "MM" ),
"Année", YEAR ( [Date] )
)
Then added a filter box with
'Calendar input'[Date]
Just to confirm my selection is efficient, I added measures to check the selected date and the count of past days
Past Days = INT(DATEDIFF(SELECTEDVALUE('Calendar input'[Date]),TODAY(),DAY))
And
Selected Date = TODAY() - [Past Days]
And these measures are correct, following my filter date selection.
I'm confident at this step, so I start a calculation in a new column but seems to not working :
Due Date Amount 0 to 30d =
VAR _PastDays = [Past Days] --My measure
--VAR _PastDays = INT(DATEDIFF(SELECTEDVALUE('Calendar input'[Date]),TODAY(),DAY))
-- I tried to calculate directly here and same result : _PastDays is empty
--VAR _PastDays = 90
-- This works, just to check my formula
RETURN
CALCULATE (
SUM ( 'Detailed Vendor Ledg_ Entry'[Amount (LCY)] ),
DATEDIFF (
'Detailed Vendor Ledg_ Entry'[Due Date],
TODAY() - _PastDays,
DAY
) <= 30,
ALLEXCEPT (
'Detailed Vendor Ledg_ Entry',
'Detailed Vendor Ledg_ Entry'[Entry No_]
)
)
Help would be much appreciated 🙂
Thank you very much in advance for your ideas on this.
