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. 

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

        Hi yuhkao .

         

        WIth the small amount of data you gave me it work correctly I did not had any issue. I just realize adding more data that I made an error on the filter the measure must be:

         

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

         

        Sorry for misleading you.