variable calculation in set analysis

[Yury]

Hi Community, 

I have Pivot table . I made dimension in rows and i see exact value that I need (number 1).

=aggr({distinct}[Period (Discount)],[Business Unit],[Country (Discount)])

Then I'm trying to add this value to [BILL AMT] measure calculation to sum amount with dates filtered by from and to using addmonths function like:

=Sum(
{$<[Creation date (billing)]=
{">=$(=addmonths (vDateEnd,-aggr({distinct}[Period (Discount)],[Business Unit],[Country (Discount)]) ))<=$(=Date(vDateEnd))"}>} 
[BILL AMT]
)

I want the "aggr()" part to be changed to 1. But it not happening... Chart ignores the statement and returns values like  =SUM([BILL AMT])

If I manually replace the "aggr()" with 1 I will get what I want, but this value should be dynamic (base on loaded data)...

=Sum(
{$<[Creation date (billing)]=
{">=$(=addmonths (vDateEnd,-1 ))<=$(=Date(vDateEnd))"}>} 
[BILL AMT]
)

I've already spent many days finding what exactly i can do and now I'm facing this issue.

Thank you in advance!

Yury.

[vikasmahajan]

Can you attach sample demo with app. Have you create a calendar in your model ?

Vikas

[vikasmahajan]

Also mention clearly what you want to achieve

[Yury]

Thank you for your response.
Unfortunately, I won't be able to attach demo, as it's pretty complex model made by our vendor.
As I found out there is no chance to make variable calculated taking dimensions in consideration.
So now I'm stuck figuring out on how to make calculation for different rows in set analysis...

vDateEnd is made via calendar and not connected to model (it's just max(selectedDate)),
I want the part
aggr({distinct}[Period (Discount)],[Business Unit],[Country (Discount)])
to be changed dynamically to 1, 3, 6 or 12 depending on row ([Business Unit],[Country (Discount)])).
[Country (Discount)] 1 - 1 [Country (Discount)]
[Country (Discount)] 1 - n [Business Unit]
[Country (Discount)] & [Business Unit] 1 - 1 [Period (Discount)]
[Period (Discount)] is coming from excel spreadsheet and it different for [Business Unit], [County (Discount)].
[cid:image002.png@01D4EB9E.BB8B5DA0]


[cid:image001.png@01D4EB9D.40292C60]