Show last 12 months data

[Anonymous]

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

[v-deddai1-msft · Accepted solution]

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]

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]

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

[jaideepnema]

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]

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]

@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 

https://www.youtube.com/watch?v=duMSovyosXE