Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a slicer that contains multiple quarters. I will like to have a formula where if i click on Q1 2020, the formula will sum up the revenue of the prior yr revenue (ie Q1 2019). One of the cards will then show current qtr revenue & another one showing last year revenue of the same qtr, then i can calculate year over year.
I had tried selectValue but it doesn't work. I hope to get some help & advice from the expert.
thanks in adv!
Solved! Go to Solution.
Hi @Anonymous ,
We can use two ways to meet your requirement.
First way,
Dates = ADDCOLUMNS (
CALENDAR ( "2018/1/1", "2020/12/31" ),
"Year Quarter Number", [date] * 100
+ QUARTER ( [date] ))
current revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__)
LQ revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__-100)
The slicer uses Dates[Year Quarter Number].
Second way,
Measure = CALCULATE(SUM('Table'[revenue]),SAMEPERIODLASTYEAR(Dates[Date]))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
BTW, the first way doesn’t need to create a relationship, and pbix file as attached.
Best regards,
Hi @Anonymous ,
We can use two ways to meet your requirement.
First way,
Dates = ADDCOLUMNS (
CALENDAR ( "2018/1/1", "2020/12/31" ),
"Year Quarter Number", [date] * 100
+ QUARTER ( [date] ))
current revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__)
LQ revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__-100)
The slicer uses Dates[Year Quarter Number].
Second way,
Measure = CALCULATE(SUM('Table'[revenue]),SAMEPERIODLASTYEAR(Dates[Date]))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
BTW, the first way doesn’t need to create a relationship, and pbix file as attached.
Best regards,
Thank you so much Dong_Li. Option 2 works! I now have another issue & hope you can guide me... i wanted to show Year Over Year Change as a % i tried creating a quick measure by dividng 'Table' [revenue] with [LY Org $] but it returns blank.
LY Org $ = CALCULATE(SUM('Table'[revenue]),SAMEPERIODLASTYEAR(Dates[Date]))
@Anonymous there are many solutions/posts on time intelligence, the key here is to add calendar dimension in your model and mark it as date table and then use time intelligence functions for the measure.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |