Dynamic Current date in pivot table

[AlanP514]

Hai all, Please help and Guide me to achieve this logic,

I want to return one pivot table it should dynamically show the current date first and previous days.

I applied the date field in the column layer at the pivot table visual and I tried using ascending and descending also, but the latest date does not start at the first 
the expected output is down:

10/3/2022

10/2/2022

10/1/2022

30/9/2022

29/9/2022

28/9/2022

27/9/2022

[AntonioM · Accepted solution]

Hi @AlanP514 ,

I would add a column to your date table and use that to sort the date field. Something like

ReverseSort = DATEDIFF([Date],TODAY(), DAY)

then sort by column

Now when you add the date to the matrix it should be sorted how you want.

[AlanP514]

Hi @AntonioM ,
When i am trying to sorting , i am getting error like this, Please support me 

Dim calendar dax above

Data model above

[AntonioM]

Hi @AlanP514 ,

Please try adding the column definition into your ADDCOLUMNS in Dim_Calendar.

[AlanP514]

Dim_Calendar =

var _FromDate= MIN(Gen_2[Updated Date])

var _ToDate=MAX(Gen_2[Updated Date])

var _Today=TODAY()

return

ADDCOLUMNS(

    CALENDAR(_FromDate,_ToDate)

    ,"Year",YEAR([Date])

    ,"Year Start Date",DATE( YEAR([Date]),1,1)

    ,"Year End Date",DATE( YEAR([Date]),12,31)

    ,"Quarter",QUARTER([Date])

    ,"Quarter Name","Q"&FORMAT([Date],"Q")

    ,"Quarter Start Date",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1)

    ,"Quarter End Date",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0)    

    ,"Year Quarter Number",COMBINEVALUES("-",YEAR([Date]),FORMAT( QUARTER([Date]),"00"))

    ,"Month",MONTH([Date])

    ,"Month Name",FORMAT([Date],"MMMM")

    ,"Month Name Short",FORMAT([Date],"MMM")

    ,"Month Start Date",DATE( YEAR([Date]), MONTH([Date]), 1)

    ,"Month End Date",EOMONTH([Date],0)

    ,"Year Month Number",FORMAT([Date],"YYYY-MM")

    ,"Year Month Name",FORMAT([Date],"YYYY-MMM")

    ,"Week of Year",WEEKNUM([Date])

    ,"Week Start Date", [Date]-WEEKDAY([Date])+1

    ,"Week End Date",[Date]+7-WEEKDAY([Date])

    ,"Year Week Number", COMBINEVALUES("-",YEAR([Date]),FORMAT( WEEKNUM([Date]),"00"))

    ,"Day",DAY([Date])

    ,"Day Name",FORMAT([Date],"DDDD")

    ,"Day Name Short",FORMAT([Date],"DDD")

    ,"Day of Week",WEEKDAY([Date])    

    ,"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1

    ,"Day Offset",DATEDIFF(_today,[Date],DAY)

    ,"Month Offset",DATEDIFF(_today,[Date],MONTH)

    ,"Quarter Offset",DATEDIFF(_today,[Date],QUARTER)

    ,"Year Offset",DATEDIFF(_today,[Date],YEAR)

)
Hai @AntonioM ,
I attached the Dax table which I used to create dim_calendar, can you please add the definition which you mentioned here, I am so confused about what you suggested.
Hoping your reply soon

[AntonioM]

Hi @AlanP514 ,

Apologies, I mean add the column as part of the   Dim_Calendar =   , so at the end like:

Dim_Calendar  =

...

...

    ,"Month Offset",DATEDIFF(_today,[Date],MONTH)

    ,"Quarter Offset",DATEDIFF(_today,[Date],QUARTER)

    ,"Year Offset",DATEDIFF(_today,[Date],YEAR)

    ,"ReverseSort",DATEDIFF([Date],_today,DAY)

)