Age Group based on selected date and due date

[Anonymous]

Hi All ,

I have a requirement in which I need to calculate the age based on the date selected in the slicer . 

Currently  I have a table which has following columns  

Acc_Table

Accounting Id
DueDate 
Reco Date

Accounting Date

InvoiceDate

Amount 
Emp name 

And a calander table which is being connected to Acc_Table on Accounting Id which is the regular calander table  with following columns 

Calander Id

Month

Year

Date.

The requirement is ,  for example if I have a invoice date as 2nd January 2018 and the Due Date as 2nd July 2019 . Both these dates are present in the matrix visualisation in the report .

Now if I select a Month and Year slicer (which has the Month and Year from the calander table , which is being connected to Accounting Id)  for example 2019 and Oct . I should be able to see the employee name with the due date age in the bracket ( age_diff - >90 days ) . The bracket is calculated by the ( due date - selected date )  , the selected date should always be the last day of the seelcted month .  

In nutshell , I should be able to see all the employees name whose invoice date is until the selected month and the amount due for them in the correct bracket (calculaated as due date - selected date )  until the selected month . 

I have tried to use dax to calculate the sum of amount until the selected month , but I am not able to fetch the due date for the employees for whihc there is no transaction in the selected month . So if there is a amount for the selected month associated with the employee on the specific month than i am able to do the calculation , but if there is no amount on the Selected Date , than i am not getting the due date and hence I am not able to find the difference and age bracket. 

Could any one please suggest a way to resolve the issue .

Please revert if the requirement is unclear .

Thanks in adavance .

[v-lionel-msft]

Hi @Anonymous ,

First, change the type of the [date] column, and create another column  in "Calander Table":

End date of month = ENDOFMONTH(Calander_Table[Date].[Date])

Then, create a measure which is used to calculate age:

Diff = 
IF(
    ISFILTERED( Calander_Table[Date]),
    DATEDIFF(
    MAX(Acc_Table[DueDate ]),
    MAX(Calander_Table[End date of month]),
    DAY
    )
)

Best regards,
Lionel Chen

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