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

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