Help with SUMX and DATESINPERIOD

[Shelley]

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.

[parry2k]

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

[Shelley]

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

✨ Follow us on LinkedIn and  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.

[Shelley]

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]

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