Clustered Column Chart

[Anonymous]

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

[parry2k]

@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]

@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 · Accepted solution]

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.