Forum Discussion

fazza1991's avatar
fazza1991
Icon for Helper II rankHelper II
4 years ago
Solved

Summarize Table for rolling 12 months

Hi,

 

Could someone help me with creating a summary table of the last 12 months data.

 

I have a table that contains months of data but i would like to create a seperate data set which automatically monthly summarises the last 12 months data set using summarize function if possible.

 

Power Query or DAX either option is fine by me

 

Thank you in advance

Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    Please do all this free training about calendar tables and offsets

    Calendar Table training

     

    Then try this ...

     

    The top graph shws sales for all months, wheres as the bootom graphs just shows the last 12 months

     

     

    How it works ..

     

    Create DAX calendar table ....

    Calendar = CALENDAR(DATE(2020,01,01),DATE(2023,12,31))

     

    A new column to the Calendar ....

    Month offset = 'Calendar'[Year offset] * 12 + (MONTH('Calendar'[Date]) - MONTH(TODAY()))
     
    Create table, matrix or graph and add a fliter with Month offste >= -11
     

     

    Thanks for reaching out for help.

    I put in a lot of effort to help you, now please quickly help me by giving kudos.

    Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

    If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

2 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    Please do all this free training about calendar tables and offsets

    Calendar Table training

     

    Then try this ...

     

    The top graph shws sales for all months, wheres as the bootom graphs just shows the last 12 months

     

     

    How it works ..

     

    Create DAX calendar table ....

    Calendar = CALENDAR(DATE(2020,01,01),DATE(2023,12,31))

     

    A new column to the Calendar ....

    Month offset = 'Calendar'[Year offset] * 12 + (MONTH('Calendar'[Date]) - MONTH(TODAY()))
     
    Create table, matrix or graph and add a fliter with Month offste >= -11
     

     

    Thanks for reaching out for help.

    I put in a lot of effort to help you, now please quickly help me by giving kudos.

    Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

    If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !