Forum Discussion

yuhkao's avatar
yuhkao
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Rolling 3 month

Hi    I would like to have rolling data for calculation of [measure] for 3 months. please help me to create it. thanks   date format is in YYYYMM format, which is text in data type now.    for ...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi yuhkao ,

     

     

    This code is a calculated column in DAX in order to have a date column to use on the rolling 3 months.

    Month_End_DAX = EOMONTH(DATE(LEFT('Table'[Date];4);RIGHT('Table'[Date];2);1);0)

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi yuhkao ,

 

Follow the steps below:

  • Add a Endofmonth column in query editor or DAX:

Power Query:

Date.EndOfMonth(#date(
    
    Number.FromText
   (Text.Start(Number.ToText([Date]),4)),Number.FromText(Text.End(Number.ToText([Date]),2)),1))

DAX:

Month_End_DAX = EOMONTH(DATE(LEFT('Table'[Date];4);RIGHT('Table'[Date];2);1);0)

 

Now add the following measure to your model:

 

Rolling 3 month = CALCULATE(SUM('Table'[Data]);DATESINPERIOD('Table'[Month_End];MAX('Table'[Month_End]);-3;MONTH) ; ALLSELECTED('Table'[Data]))

 

Should return expected result.

 

  • yuhkao's avatar
    yuhkao
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    MFelix 

     

    DAX:

    Month_End_DAX = EOMONTH(DATE(LEFT('Table'[Date];4);RIGHT('Table'[Date];2);1);0)

     

     

    is it  

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi yuhkao ,

       

      Don't understand what you mean.

       

      What is your question?

      • yuhkao's avatar
        yuhkao
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        hi MFelix 

         

        sorry i think system was error.

         

        my question is DAX for create table? column? measure?

        thanks 

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi yuhkao ,

       

       

      This code is a calculated column in DAX in order to have a date column to use on the rolling 3 months.

      Month_End_DAX = EOMONTH(DATE(LEFT('Table'[Date];4);RIGHT('Table'[Date];2);1);0)

       

      • yuhkao's avatar
        yuhkao
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        MFelix 

         

        Hi MFelix - as i used your method to generate rolling 3 month data. i place into table visual with time frame, why it only shows last 3 month rolling as total? for previous couples months, it only shows month data instead of rolling data.