Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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 |
---|---|
76 | |
76 | |
56 | |
37 | |
34 |
User | Count |
---|---|
99 | |
56 | |
54 | |
45 | |
40 |