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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Roym
Helper IV
Helper IV

Create measure to show if value is higeher, lower or unchanged

I have a dashboard with a slicer to select the period (1 or 2) and the year. You can make two selections in this slider, so for example 2 2023 and 1 2024. In that dashboard I have the following table:

 

  

Name    Period   Year        Period-Year-code             Value     Change

Item 1   2             2023          2                                    25         

Item 1   1             2024     3                                          15          Lower

 

 So what I'm looking for is the change value that is in the last column. For every selection you make in the slicer there will be two results with the same name, only the period-year-value are different. Im struggeling to come up with a logic that would work for this. Hopefully anyone has an idea how to calculate this. Thanks!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Roym , If you have date or create date using year and month

Date = Date([Year], [Month], 1)

 

Join with Date of date table and use time intelligence

 

then you can have measure like that have diff measure

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

 

Or you can measure based on min and max selected

 

Mtd =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = eomonth(_max,-1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

LMtd =
var _max = eomonth(maxx(allselected('Date'),'Date'[Date]),-1)
var _min = eomonth(_max,-1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

 

 

or you can use two slicer

 

 

Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Roym , If you have date or create date using year and month

Date = Date([Year], [Month], 1)

 

Join with Date of date table and use time intelligence

 

then you can have measure like that have diff measure

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

 

Or you can measure based on min and max selected

 

Mtd =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = eomonth(_max,-1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

LMtd =
var _max = eomonth(maxx(allselected('Date'),'Date'[Date]),-1)
var _min = eomonth(_max,-1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )

 

 

or you can use two slicer

 

 

Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Perfect thanks! I was able to figure it out with your explanation.

Helpful resources

Announcements
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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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