challenge using two dates in enitre Report( date , effective date)

[gauri]

Hi all

I am in a fix how to solve this. Following is the issue :

In my Report i have some visuals which are  responsive to my calendar date tabel. In the image below

  The top card beside Average Past Due which is blank will contain the DSO calculated in it, and for calculating my DSO i have my revenue which is responsive from calendar date table and i have my aging amount which will be based on aging date. When i say aging date i mean a historical Aging, like if i am finding a DSO for JAN 2020 i will run my aging for 1-01-2020  and i will consider the total aging amount from this visual. So i am in a fix how can i use these two dates in a same visual or is their any other way doing this. Also can we get the historical aging ( where we can run aging for any period like jan , feb or may) without passing a parameter at the desktop and service, Or if i select my start date from the slicer then it should dyanamically change my calculated column which is calculated in the follwoing way :

Month = DATEDIFF(pcc_view_ar_aging[effective_date], pcc_view_ar_aging[Column1],MONTH).

My Column1 should hold the date which i will select in the slicer.

Regards,

Gauri P.

[MFelix]

Hi @gauri ,

For this you need to make use of a filtering option within the measure itself depending on the way you are calculating should be similar to:

Measure = CALCULATE(SUM(Table[Column]);FIlter(ALL(Table[Date]);Table[Date] <= MAX(Calendar[Date]) && Table[Date] >= DATEADD(MAX(Calenda[Date]);-1;year))

Be aware that this measure was done by heart may need some adjustments, the sum should be replace by your calculation.

[gauri]

@MFelix  Can you explain that dax ( the -1 part)

[MFelix]

On DATEADD function the second parameter is the number of periods you want to go back or front in this case I'm calculating previous year date so -1 year.