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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.