Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |