Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello!
I'm trying to compare a quarters and year (Selected from slicer) to the year before the selected one.
This is the measure I've written to accomplish this:
Productivity with Variables =
VAR PickedYear = SELECTEDVALUE( 'Calendar'[Year] )
VAR PickedQuarter = SELECTEDVALUE( 'Calendar'[Quarter] )
VAR SelectedYTD =
FILTER(
ALL('Calendar'[Quarter],'Calendar'[Year]),
AND(
'Calendar'[Year] = PickedYear,
'Calendar'[Quarter] <= PickedQuarter
)
)
VAR YearBefore =
FILTER(
ALL( 'Calendar'[Year] ),
'Calendar'[Year] = PickedYear - 1
)
VAR MHworked =
[Man-hours worked - Staff registered Actual SUM]
+ [Man-hours worked - Workers registered Actual SUM]
+ [Manhours worked agency people SUM]
VAR VAatSC = [Value added at SC SUM]
RETURN
DIVIDE(
DIVIDE(
CALCULATE(
VAatSC,
SelectedYTD
),
CALCULATE(
MHworked,
SelectedYTD
),
0
),
DIVIDE(
CALCULATE(
VAatSC,
YearBefore
),
CALCULATE(
MHworked,
YearBefore
),
0
)
)
However, this measure always returns 1 for every choise I make.
Why is that?
What should I do instead?
Thank you for your help!
@Anonymous , you use time intelligence, assuming you have a date
example
Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
or
This qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Qtr] = Max('Date'[Qtr]) ))
Lat year qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Qtr] = Max('Date'[Qtr])))
This is almost what I am looking for.
These measures works if I just want to look at the last quarter right?
What if I want to be able to see different periods? Like Q3 2019?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
80 | |
60 | |
35 | |
35 |
User | Count |
---|---|
100 | |
60 | |
56 | |
46 | |
41 |