Last 4 weeks data

[Narasimha]

Hi team..i want to display last 4 weeks data in column chart. At any point it is showing 4 weeks data.
Could you please help me with step by step process.

[Narasimha]

I was created new calculated column with below DAX

Last4Weeks = IF(DATEDIFF(ClosedTickets[PResolveDate],TODAY(),WEEK)<5 && MONTH(ClosedTickets[PResolveDate]) <> WEEKNUM(today()),"YES" ,"NO")

[v-yulgu-msft]

Hi @Narasimha,

Does above suggestion as I mentioned in my original post apply to your scenario? If you have resolved your issue, please kindly mark the corresponding reply as an answer so that it can benefit more people. If you still have any question, please feel free to ask.

Regards,
Yuliana Gu

[Narasimha]

Hi Team,

Am getting below results while using 4weeks DAX formula,

From above Image, Am getting extra week i.e Week28.. How to exclude Week28. I just display Last4Weeks data only.

Thnaks

Narasimha Reddy

[Narasimha]

Bumping up....

[Liron]

Hi,

Thank you for this solution.

I have a simalar problem, I'll try my best to explain.

I want to present the weekly measures of the 4 last weeks, the only difference is the I want it to be filtered by a slicer- to show only the 4 weeks back from the date on the slicer. (that the last week presented would be the date on the slicer, that it would be moving).

in my data I have both weekly and rolling 28 (4 last weeks) aggregated data.

I have a slicer of dates called 'period ends on', and I want it to show the 4 last weeks according to the slicer.

How can I do it?

Thanks a lot,

Liron

[Narasimha]

Can any one ping me v-lavala in pst hours..so that will work to resolve the issue
Else give ur alias..will ping me over im
Thanks
Narasinha

[v-yulgu-msft]

Hi @Narasimha,

Do you want to always display the last 4 weeks data in column chart?

Suppose there are these columns in source table: ClosedTickets[PResolveDate], ClosedTickets[Amount], ClosedTickets[Category]. Then, you should create a measure using this formula:

Sum Amount =
CALCULATE (
    SUM ( ClosedTickets[Amount] ),
    FILTER (
        ClosedTickets,
        DATEDIFF ( ClosedTickets[PResolveDate], TODAY (), WEEK ) < 5
    )
)

Drag this measure into value section.

If I have something misunderstood, please correct me.

Best regards,
Yuliana Gu

[Phil_Seamark]

Hi @Narasimha

Do you mean a rolling sum of the previous 4 weeks?  

[alanhodgson]

Hey @Narasimha,

First thing you want to do is make a new calculated column to hold the last day of every week:

Last Day of Week = Query[Date]+MOD(8-WEEKDAY(Query[Date],1),7)

Then, you can use the "Last Day of Week" (it is End Week2 in my dataset) column to create a filter on the chart. "End Date" in my data, should be the date you use in the formula above. This will show the last 4 weeks of the latest selected date in your slicer.

See below for the set up:

Hope this helps,

Alan

[Anonymous]

Hi @alanhodgson

Your soloution matches my requirment, but it is not working for me. I have created caluculated coloumnas like you said

Last Day of Week2 = 'Datekey'[Date]+MOD(8-WEEKDAY('Datekey'[Date],1),7)

[alanhodgson]

Hey @Anonymous,

Make sure you are putting the Date field that you use for the calculated column into the "Axis" part of the visual.

Other than that, it looks correct to me. I was also able to recreate the same column again and it worked fine.

Hope this helps,

Alan