Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Clustered Column Chart

Hello everyone,

i have 3 columns Name, Last Reviewed Date, Todays Date

i have created a calumn Week since last reviewed by using dax  DATEDIFF('Table'[Last Reviewed Date],'Table'[Todays Date],WEEK))

 

 

i have to created below visual from above date for that purpose i have created a calulated column using below dax

Week since last reviewed = IF(ISBLANK('Table'[Week since last reviewed]),"Not Reviewed",IF('Table'[Week since last reviewed] > 4, "More than 4 Weeks",'Table'[Week since last reviewed]))

 

 i have to create below visual which shows time since last reviwed vs count in names column

i tried to create the same in power bi but  one dates Apr-12 & Apr 05  snice reviewe happend those date are not in the table.

i am geeting below visual in power bi

i cannot group more than 4 weeks  

need assistance on this

 

Regards

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Try to create column like this and filter not blank value in the visual:

    A = 
IF (
    WEEKNUM ( 'Table'[Last Reviewed Date], 2 )
        >= WEEKNUM ( TODAY (), 2 ) - 3 ,
    CONVERT ( FORMAT ( 'Table'[Last Reviewed Date] - MOD('Table'[Last Reviewed Date] ,7) + 6, "MMM - DD" ), STRING ),
    IF (
        ISBLANK ( 'Table'[Week since last reviewed] ),
        "Not Reviewed",
        IF ( 'Table'[Week since last reviewed] > 4, "More than 4 Weeks" )
    )
)

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous you have to add date dimension in your model which will have all the weeks and use that on the x-axis and perform all weeks-related logic from that dimension.

     

    It is a best practice to have a date dimension in your model when working with time intelligence. I have a blog post that talks about how you can easily add one. Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      parry2k Thanks for the reply i have created the date table and given one to many relationship date[date] to Table[Name]

      i have got below visual for axis i have used (Date[week]) and for values i have used count of (Table[Name]) 

      i need the visual like below where May 21 - Current week (week 21) , May 14 - Previous week (week 20)  &  May 7 - Previous previous week (week 19) 

      Not reviwed are blanks and  others are more than 4 weeks

       

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi Anonymous ,

        Try to create column like this and filter not blank value in the visual:

        A = 
IF (
    WEEKNUM ( 'Table'[Last Reviewed Date], 2 )
        >= WEEKNUM ( TODAY (), 2 ) - 3 ,
    CONVERT ( FORMAT ( 'Table'[Last Reviewed Date] - MOD('Table'[Last Reviewed Date] ,7) + 6, "MMM - DD" ), STRING ),
    IF (
        ISBLANK ( 'Table'[Week since last reviewed] ),
        "Not Reviewed",
        IF ( 'Table'[Week since last reviewed] > 4, "More than 4 Weeks" )
    )
)

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.