cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## Repeat result of calculation accross dates

Hi all,

I have a file where I have several subscriptions the frequency of which can vary ( you can have a quarterly, yearly etc. subsciption) and I have the first date when a subscriber has to pay the amount for the subscription.
I'd like to show the sum of amount by each month when a subscription fee is due, e.g.:
Invoice date is in July and its a quarterly subsription, then I'd like to show the fee for this sub in July and also in October (a quater, 3 months later) and also in January and so on.

I also have multiple date columns in my table and a calendar table. There's no active relationship between the dates and the calendar table.

My logic to identify in which month to categorize certain subs is this:
If difference in months between invoice date and current date can be divided by the lenght of subscription without remainder value then it's going to return the value of the subscription, otherwise it's going to be blank.
Basically:

Measure =
varlastdate = LASTDATE ( Calendar[Date] )
var calcualtion =
IF(
MOD(
DATEDIFF ( MAXX(Table, Table[Invoice_Date]), var_lastdate, MONTH ),
MAXX ( Table, Table[Invoice_Frequiency_in_months] )
) = 0,
CALCULATE(
SUM ( Amount ),
USERELATIONSHIP ( Calendar[Date], Table[Invoice_Date])
)
)

This is going to show the value for the months in Invoice_Date column, and not assume it goes wrong when I activate the relationship between my table and the calendar table.

Any idea how to solve this? Theer's also a chance that I overcolplicate things with the above logic and ther's a more stratighforward way to do this.

Thanks in advance!

CreationDateOtherDateInvoiceDateInvoice_Frequiency_in_monthsAmount

 2022-07-04 3 5 2022-07-04 6 10 2022-08-04 3 23 2022-10-04 12 15 2022-06-04 12 2 2022-08-04 3 1 2022-09-04 6 4 2022-10-04 6 8 2022-07-04 3 12 2022-08-04 12 100
1 ACCEPTED SOLUTION
Regular Visitor

So the main issue was to repeat the calcualtion a certain amount of times, basically I needed a for loop.

The solution is to use the DATEADD function to shift the peiods by the given frequency and to repeat this I used GENERATESERIES, the second parameter of which is the number of iterations you need (I needed this for the next 2 years which is 8 quarters, that's why I have 8 in there).

I think it would also work with PARALELLPERIOD instead of dateadd too.

Measure =
SUMX(
GENERATESERIES(1,8,1),
VAR CurrentValue = [Value]

VAR Quarterly_Subs =
CALCULATE(
[Amount],
DATEADD('Date'[Date], -3 * CurrentValue, MONTH),
'Table'[Invoice_Frequiency_in_months] = 3
)
VAR SemiAnnual_Subs =
CALCULATE(
[Amount],
DATEADD('Date'[Date], -6 * CurrentValue, MONTH),
'Table'[Invoice_Frequiency_in_months] = 6
)
VAR Annual_Subs=
CALCULATE(
[Amount],
DATEADD('Date'[Date], -12 * CurrentValue, MONTH),
'Table'[Invoice_Frequiency_in_months] = 12
)
RETURN
Quarterly_Subs +
SemiAnnual_Subs +
Annual_Subs
)

6 REPLIES 6
Regular Visitor

So the main issue was to repeat the calcualtion a certain amount of times, basically I needed a for loop.

The solution is to use the DATEADD function to shift the peiods by the given frequency and to repeat this I used GENERATESERIES, the second parameter of which is the number of iterations you need (I needed this for the next 2 years which is 8 quarters, that's why I have 8 in there).

I think it would also work with PARALELLPERIOD instead of dateadd too.

Measure =
SUMX(
GENERATESERIES(1,8,1),
VAR CurrentValue = [Value]

VAR Quarterly_Subs =
CALCULATE(
[Amount],
DATEADD('Date'[Date], -3 * CurrentValue, MONTH),
'Table'[Invoice_Frequiency_in_months] = 3
)
VAR SemiAnnual_Subs =
CALCULATE(
[Amount],
DATEADD('Date'[Date], -6 * CurrentValue, MONTH),
'Table'[Invoice_Frequiency_in_months] = 6
)
VAR Annual_Subs=
CALCULATE(
[Amount],
DATEADD('Date'[Date], -12 * CurrentValue, MONTH),
'Table'[Invoice_Frequiency_in_months] = 12
)
RETURN
Quarterly_Subs +
SemiAnnual_Subs +
Annual_Subs
)

Community Support

Hi @PDG_VL,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Hi Xiaoxin @v-shex-msft ,

the samle data is at the bottom of my post, it seems that it's been reformatted by the forum so I'm readding it now also adding a table with expected results.

 CreationDate OtherDate InvoiceDate Invoice_Frequiency_in_months Amount ID 6/4/2022 12 2 1 7/4/2022 3 5 2 7/4/2022 6 10 3 7/4/2022 3 12 4 8/4/2022 3 23 5 8/4/2022 3 1 6 8/4/2022 12 100 7 9/4/2022 6 4 8 10/4/2022 12 15 9 10/4/2022 6 8 10

 Expected result Date Amount Invoiced_Ids 2022-May 0 - 2022-Jun 2 1 2022-Jul 27 2,3,4 2022-Aug 124 5,6,7 2022-Sep 4 8 2022-Oct 40 9,10,2,4 2022-Nov 24 5,6 2022-Dec 0 - 2023-Jan 27 2,3,4 2023-Feb 24 5,6 2023-Mar 4 8 2023-Apr 25 10,2,4 2023-May 24 5,6 2023-Jun 2 1 2023-Jul 27 2,3,4 2023-Aug 124 5,6,7

Thanks

Community Support

HI @PDG_VL,

You can use the date field with 'date hierarchy' mode and keep the 'year' and 'month' levels to use on the table visual and turn on the 'show items with no data' option, then add the 'amount' field with aggregate mode 'sum'.

After these steps, you can write a measure formula to show the corresponding id list based on current row contexts:

formula =
CONCATENATEX ( VALUES ( 'Table'[ID] ), [ID], "," )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Thanks,but it still doesn't solve the maien issue: repeat values based on frequency.
I have the solutions though, will post it separately.

Regular Visitor

I made some progress, but the situation is still not ideal.

So using paralellperiod I managed to move one period,however, I need to create a separate calcualtion for each subscription type (quarterly, semi annualy, annualy etc.) and it only works for one period ahead and I'd need to calcualte this for the next 2 years.

Quarterly=

CALCULATE(
SUM ( Amount ),
Invoice_Frequency_in_months = 3,
USERELATIONSHIP ( Calendar[Date], Table[Invoice_Date]),
PARALELLPERIOD(Calendar[Date], -3, MONTH)

)

Semi Annualy =

CALCULATE(
SUM ( Amount ),
Invoice_Frequency_in_months = 6,
USERELATIONSHIP ( Calendar[Date], Table[Invoice_Date]),
PARALELLPERIOD(Calendar[Date], -6, MONTH)

)

## Helpful resources

Announcements

#### Check it Out!

Click here to learn more about the August 2022 updates!

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors