Forum Discussion

Martial_Bon's avatar
Martial_Bon
Frequent Visitor
3 years ago

Help - Report with variable calculation date

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.

8 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi Martial_Bon 

    What do get when try

    Due Date Amount 0 to 30d =

    CALCULATE (

        SUM ( 'Detailed Vendor Ledg_ Entry'[Amount (LCY)] ),

        DATEDIFF (

            'Detailed Vendor Ledg_ Entry'[Due Date],

            TODAY() -[Past Days],

            DAY

        ) <= 30,

        ALLEXCEPT (

            'Detailed Vendor Ledg_ Entry',

            'Detailed Vendor Ledg_ Entry'[Entry No_]

        )

    )

    • Martial_Bon's avatar
      Martial_Bon
      Frequent Visitor

      Thank you very much for your answer tamerj1 !

      The return value calculate considering TODAY() because [Past Days] is empty.

       

      edit :

      I replied too quickly sorry ! If I use directly the measure [Past Days], I have a PLACEHOLDER error message :

       

      >> Une fonction « PLACEHOLDER » a été utilisée dans une expression True/False utilisée en tant qu'expression de filtre de table. Cela n'est pas autorisé.

       

       

      Martial

      • tamerj1's avatar
        tamerj1
        Community Champion

        Martial_Bon 

        Would tou please double check if a relationship has been accidentally created between the two tables?

  • Martial_Bon's avatar
    Martial_Bon
    Frequent Visitor

    Hi community, any new idea to help me to resolve my issue ?

    🙏