Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Date difference between a date column in a table and a dynamic custom date :

I have a table with a date column Invoice Table (Invoice Id, Invoice Date, Invoice amount)...I need a new custom date attribute (say 'Effective Date') which will take input from the filter selection by user . Now I need to find the' Invoice_Age' . Which is the difference of Invoice Date from each record and Effective Date input. I am using Direct Query in Power Bi desktop. 
Please suggest me right way to achieve this. 


Details Updated:-
Invoice Table 


I need to create a date attribute called 'Effective Date' that can take single date input from user from filter panel and use that date to calculate 'Invoice Age' and create a report as below. Also I need to use this Invoice Age to create some group bins .
If Effective Date selected by user from filter :  01-02-2023

Please suggest a solution for this complete requirement Mahesh0016 






2 Replies

  • Anonymous Please Share Demo Dataset and Expected Output in Table.

    THANK YOU!!

  • Anonymous Please reffer below code

    Days =
    CALCULATE (
    [Sum Of Sale],
    FILTER (
    ALL ( 'Table Y'[Date] ),
    DATEDIFF ( 'Table Y'[Date], MAX ( 'Table Y'[Effective Date] ), DAY ) < 31
    && DATEDIFF ( 'Table Y'[Date], MAX ( 'Table Y'[Effective Date] ), DAY ) >= 0
    )
    )


    Anonymous If this post helps you please mark as solutions and give the Thump/kudos.THANK YOU!!