Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Shelley
Continued Contributor
Continued Contributor

Help with SUMX and DATESINPERIOD

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)]

_Design =
CALCULATE([POS Amt (USD)], 'Products'[LifeCycle Services Phase] = "Design")
 
I tried this, but it doesn't seem to be calculating correctly and I think it may be because I do not have the SUMX function.
_YTD Design $ =
CALCULATE([_Design],
(DATESINPERIOD('Fiscal Calendar'[Date], MAX('Billings'[Bill Date]), -2, YEAR)))

Is anyone able to tell me how to write the [_YTD Design $] expression so it properly sums up the iteration of each row?

Thanks in advance.
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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 )

 

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

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.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@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!!

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

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
Continued Contributor
Continued Contributor

@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!

parry2k
Super User
Super User

@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 )

 

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

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
Continued Contributor
Continued Contributor

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:

_YTD Design $ =
VAR _1 = CALCULATE ( MAX ( 'Billings'[Bill Date] ) , REMOVEFILTERS() )
VAR _2 = DATESINPERIOD ( 'Fiscal Calendar'[Date] , _1 , -2 , YEAR )
VAR _3 = CALCULATE([POS Amt (USD)], 'Products'[LifeCycle Services Phase] = "Design")
RETURN
CALCULATE ( _3 , 2 )
 
And I receive the same error.

I probably need to provide all details for you or anyone else to help me, but this is so complicated and I cannot share any data as it is confidential, it will take a lot of time and then if I do this and nobody is able to help, then I just wasted a ton of time. I really dislike having to use another team's AAS model for something else that was not the original intention of the AAS model. It feels like I'm pounding a square peg into a round  hole. Thanks again for the help!



TheoC
Super User
Super User

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors