Forum Discussion

Narasimha's avatar
Narasimha
Helper I
9 years ago

Last 4 weeks data

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.

11 Replies

  • alanhodgson's avatar
    alanhodgson
    Solution Supplier
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        alanhodgson
        Solution Supplier
        9 years ago

        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

  • Narasimha's avatar
    Narasimha
    Helper I
    9 years ago

    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's avatar
      v-yulgu-msft
      Microsoft Employee
      9 years ago

      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

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      9 years ago

      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

      • Liron's avatar
        Liron
        New Member
        9 years ago

        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