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 need a quick fix to a seemingly simple issue here :
Measures are Rejection rate and Invoice count.
I have a Year Slicer.
1. When I select the year I want to display a measaure: Rejection rate for selected year and two preceding years (2021, 2020, 2019)
2. I also want to be able to display another measure: Invoice count for all years before 2021.
Below is my simple solution (using a table) for years before 2021.
Solved! Go to Solution.
@Anonymous I think @danextian already have a measure for this but need a bit of tweaking before the selected year need < (less than sign) not less than equal to (<=) otherwise that will include selected year value as well:
Total Before Selected Year =
CALCULATE (
SUM ( 'Fact'[Value] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] < SELECTEDVALUE ( 'Disconnected Calendar'[Year] )
)
)
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
@Anonymous I think @danextian already have a measure for this but need a bit of tweaking before the selected year need < (less than sign) not less than equal to (<=) otherwise that will include selected year value as well:
Total Before Selected Year =
CALCULATE (
SUM ( 'Fact'[Value] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] < SELECTEDVALUE ( 'Disconnected Calendar'[Year] )
)
)
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
@Anonymous I agree and endorse the @danextian solution. I haven't looked at his file but his reply is on the right track.
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.
Hi @Anonymous ,
My approach would be to use a disconnected Dates table (one that has not relationship to any of of the Fact or Dates tables). Please see sample PBIX. https://drive.google.com/file/d/1ktSLpKFEjGIzLb3RzcIqxY75AbheS_iS/view?usp=sharing
Proud to be a Super User!
Awesome! Yes, this works.
I attempted disconnecting the date table by disabling interactivity between the Slicer and the table/graph.
What dax would you apply for graph of measure count for ALL years before selected year (2021) ?
Hi @Anonymous ,
This was my formula in the attached file
Total Before Selected Year =
CALCULATE (
SUM ( 'Fact'[Value] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] <= SELECTEDVALUE ( 'Disconnected Calendar'[Year] )
)
)
@parry2kis right. It should have been ( just < instead of <=)
Total Before Selected Year =
CALCULATE (
SUM ( 'Fact'[Value] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] < SELECTEDVALUE ( 'Disconnected Calendar'[Year] )
)
)
Proud to be a Super User!
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
No, it does not help. I need a DAX formular to solve both cases instead.
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 |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |