Forum Discussion

AlanP514's avatar
AlanP514
Post Patron
3 years ago
Solved

Dynamic Current date in pivot table

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 t...
Need Help
Show and Tell
Tips and Tricks
  • AntonioM's avatar
    AntonioM
    3 years ago

    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.

     

     

AntonioM's avatar
AntonioM
Solution Sage
3 years ago

Hi AlanP514 ,

 

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

AlanP514's avatar
AlanP514
Post Patron
3 years ago
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's avatar
    AntonioM
    Solution Sage
    3 years ago

    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)
    )