Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

6 REPLIES 6
v-deddai1-msft
Community Support
Community Support

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
Super User
Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

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
Solution Sage
Solution Sage

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
Not applicable

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

 

@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 



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.