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

Top Solution Authors