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 ,

 

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

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