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 select...
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

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