Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Show last 12 months data

Hi ,

I need to display Last 12  months data for a measure deoending on Year and Month selection.

I managed to write the measure, but when put in the Line chart, it gets filtered only for the selected month.

 

Any help is appreciated

 

Regards,

Priyanga

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    Did you have a dim_ date table? I suggest you create a date table and then you can use the following measure:

     

    Last 12 months Sales =
VAR Start_date =
    CALCULATE ( MAX ( Dim_Date[Date] ), ALLSELECTED ( Dim_Date ) )
RETURN
    CALCULATE (
        [Measure],
        ALL ( Dim_Date ),
        DATESINPERIOD ( Dim_Date[Date], [Start_date], -12, MONTH )
    )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

6 Replies

  • jaideepnema's avatar
    jaideepnema
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Anonymous ,

    Not clear much....can you please share your DAX code for the measure and if possible a sample file with sensitive data removed ?

     

    Regards,

    Jaideep

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jaideep,

       

      I cannot attach sample pbix file due to security retrictions. But I have put down the measures
      Calculated Column: 

      12 months ago = DATEADD(Sheet5[Date],-12,MONTH)

      Measure for start date and end date for selected YEAR_MONTH
      Start date = CALCULATE(MAX( Sheet5[12 months ago]),ALLSELECTED(Sheet5))
      End date = MAX(Sheet5[Date])

      Last 12 months Sales measure:
      Last 12 months =
      CALCULATE (
      SUM ( Sheet5[Measure]),
      ALL ( Sheet5),
      DATESBETWEEN (
      Sheet5[Date],
      [Start date],
      [End date])
      )
       
      When I try to put the Last 12 months measure in Line graph, instead of showing data for Last 12 months, it gets filtered  only for the selected month in the slicer. I have both YEAR and MONTH slicer in my report.

       

       

      Regards,

      Priyanga

       

      • negi007's avatar
        negi007
        Icon for Community Champion rankCommunity Champion
        5 years ago
        Anonymous You can create a measure like below
         
        TTM_Sales =
        VAR CurrentDate = MAX('Date'[End of Month Date])

        VAR PreviousDate = CurrentDate - 365
        VAR Result =
        CALCULATE(
        SUM(FactInternetSales[SalesAmount]),
        FILTER(
        FactInternetSales,
        FactInternetSales[End of Month Date] >= PreviousDate && FactInternetSales[End of Month Date] <= CurrentDate
        )
        )
        Return Result
         
        refer to below video for reference 
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    hi, Anonymous 

    If it is OK with you, please share your sample pbix file, or your measure. Then I can try to come up with a more accurate solution.

     

    thank you.

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jihwan,

      I cannot attach sample pbix file due to security retrictions. But I have put down the measures
      Calculated Column: 

      12 months ago = DATEADD(Sheet5[Date],-12,MONTH)

      Measure for start date and end date for selected YEAR_MONTH
      Start date = CALCULATE(MAX( Sheet5[12 months ago]),ALLSELECTED(Sheet5))
      End date = MAX(Sheet5[Date])

      Last 12 months Sales measure:
      Last 12 months =
      CALCULATE (
      SUM ( Sheet5[Measure]),
      ALL ( Sheet5),
      DATESBETWEEN (
      Sheet5[Date],
      [Start date],
      [End date])
      )
       
      When I try to put the Last 12 months measure in Line graph, instead of showing data for Last 12 months, it gets filtered  only for the selected month in the slicer. I have both YEAR and MONTH slicer in my report.

       

       

      Regards,

      Priyanga

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Did you have a dim_ date table? I suggest you create a date table and then you can use the following measure:

     

    Last 12 months Sales =
VAR Start_date =
    CALCULATE ( MAX ( Dim_Date[Date] ), ALLSELECTED ( Dim_Date ) )
RETURN
    CALCULATE (
        [Measure],
        ALL ( Dim_Date ),
        DATESINPERIOD ( Dim_Date[Date], [Start_date], -12, MONTH )
    )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai