Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello All, I just can't seem to figure this out. I have a measure that I need to sum based on another measure in each row of data. I also need to sum it up for the last two years dynamically. I'm thinking I need to use SUMX and DATESINPERIOD, but I cannot figure out how to type this to make this work.
This measure is also based on a measure [POS Amt (USD)]
Solved! Go to Solution.
@Shelley without knowing the details the following should work. First, you cannot apply filters if the value is stored in a variable, if the syntax was correct, you were never going to get a correct result.
_YTD Design $ =
VAR __LastBillDate = CALCULATE ( MAX ( 'Billings'[Bill Date] ) , REMOVEFILTERS() )
VAR __TableDates = DATESINPERIOD ( 'Fiscal Calendar'[Date] , __LastBillDate, -2 , YEAR )
RETURN
CALCULATE([POS Amt (USD)], 'Products'[LifeCycle Services Phase] = "Design", __TableDates )
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
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.
@Shelley Glad, I could help. 👏Feel free to subscribe to my YouTube channel, maybe my videos already have a solution for your next challenge. Cheers!!
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
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.
@parry2k Thank You! So, my original formula worked for its measure and where it was falling down was in my next step where I then had to count the number of customers with Design $. So, even though it was the count formula without the correct result, I thought it was due to this $ calculation. When I first read your solution, I knew it seemed promising. (even though I'm not smart enough to figure it out myself). Anyway, once implemented, your suggestion seems to have done the trick! THANK YOU SO MUCH! Now on to my next issue.... Have a good day!
@Shelley without knowing the details the following should work. First, you cannot apply filters if the value is stored in a variable, if the syntax was correct, you were never going to get a correct result.
_YTD Design $ =
VAR __LastBillDate = CALCULATE ( MAX ( 'Billings'[Bill Date] ) , REMOVEFILTERS() )
VAR __TableDates = DATESINPERIOD ( 'Fiscal Calendar'[Date] , __LastBillDate, -2 , YEAR )
RETURN
CALCULATE([POS Amt (USD)], 'Products'[LifeCycle Services Phase] = "Design", __TableDates )
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
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.
Thanks, @TheoC. I tried as written and get an error:
The report measure has a syntax or semantic error at line 6, position 1, reported by analysis services: The true/false expression does not specify a column. Each true/false expression used as a table filter expression must refer to exactly one column.
I then tried this:
Hi @Shelley
Can you try the following:
_YTD Design $ =
VAR _1 = CALCULATE ( MAX ( 'Billings'[Bill Date] ) , REMOVEFILTERS() )
VAR _2 = DATESINPERIOD ( 'Fiscal Calendar'[Date] , _1 , -2 , YEAR )
VAR _3 = [_Design]
RETURN
CALCULATE ( _3 , 2 )
I apologise as I haven't tested syntax. Note that you may need to replace _3 with the complete _Design measure and also define the [POS Amt (USD)] measure within the _3.
Let me know how it goes.
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
98 | |
39 | |
30 |