Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Group data for previous year

Hi,

 

I want to bulild a simple column chart.

For the moment I have data for first monday of the week.

I want, staring next year to show the first bar as an average of the previous year and the following bars as daily (monday) value.

How can I do it. I'm not experienced with Dax at all. It is like a grouping, right?

All data is in a plane table.

 

Thank you.

Need Help

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    2 years ago

    can you provide some sample data with an example of your expected result?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I tried to create a dataset. So, the average on 2023 (2022, 2021 and so on) should be listed in the first bar, and then, the values for the months from this year should be like in the picture :).

     

    Thanks 

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      2 years ago

      doesnt look like that average is correct on your 2023 bar, but im probably being picky. 🙂

       

      do you have an actual date field?  please post the data in text format, or provide a cloud source link to your pbix, unforunately i am unable to use images so providing sample data is important.

       

      It is really easy to do what you want but having a date field that links to a calendar table will solve the issue.  You can transform your period date into a proper date if you need to,but be easier to have a date field.

       

      here is how you can create a date table, a date table will allow you to visualise the dates as expected. 

      https://www.youtube.com/watch?v=-li7sxUxEqA 

       

      If you want an example of how its done please provide / paste your data preferably with a date field if you have one, in text format or linked via an external source.  

       

      Even just changing your current period field to a date for example 1 of each month, if you have auto datetime switched on (which i dont necessarily recommend)  you will be able to use a hierarchy for your date

       

      however the average that you have there at the year level, you can potentially drill up and down, from year to year month and get that average, an other way to plot average is to use a combo chart and plot the average on a line

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you for answer.

    The data that I pasted is an example. I can't paste real data here for sure.

    I'm tracking data starting Week 15 this year. I just need to make it "future proof".

    I intend to transform W15 (now I have it as string) to a date but this will happen starting to january.

     

    I'm still not getting how can I add one bar with an average value of the last 12 month and then, the next ones showing current month (week...).

    Thank you for your time.