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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RaghdahIsmael
Helper I
Helper I

How to return values of previous month this year and previous month previous year

Hello All,

 

i have a question that includes 2 parts :

 

1.   i need please 2 measures ; one return SUM of values of Previous Month this Year, and the second Previous Month Previous year and this needs to be filterd automatically by the Date Field (Month,Year) withiout having me filtering this manually in the filters bar on the right. Each measrue will be dragged in each visula as the below screenshot. As you can see below i created the Sum of Items and dragged it into the Values box, so this feild needs to be used in both measures .

 

RaghdahIsmael_0-1664484603062.png

2.    The second part, i need the visual Title to be changed automatically based on the above measures, so if we are in August the title needs to be July 2022 on th right visual and July 2021 on the left visual.

 

thank you, Capture.JPG

1 ACCEPTED SOLUTION
ahmadibrahimbus
Resolver III
Resolver III

you create three calculated columns one to get the month name and the second to get the year and one for month year.
so you will be able to slice on these two columns.

after the user select the month and year the blow measures will works well for you:

Previous month last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))
Previous month this year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

and the below two measures are for the month name
previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

previous Month Name previous year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))

so you can append the last two measures to text box .
please let me know if it solved your needs.

View solution in original post

4 REPLIES 4
RaghdahIsmael
Helper I
Helper I

The below measure seems not to be implementing correctly as it gave me December 2022 while it show show September 2022 as the current month.

 

previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

RaghdahIsmael_0-1664488737381.png

 

ahmadibrahimbus
Resolver III
Resolver III

you create three calculated columns one to get the month name and the second to get the year and one for month year.
so you will be able to slice on these two columns.

after the user select the month and year the blow measures will works well for you:

Previous month last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))
Previous month this year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

and the below two measures are for the month name
previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

previous Month Name previous year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))

so you can append the last two measures to text box .
please let me know if it solved your needs.

Thank indeed Ahmed,

 

the results came just as excepted only after i changed the date field to be freom the Calendar table. previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

and everything else worked too.

thank you indeed , i will try them out and let your know. 

 

Thanks for the rapid response

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors