cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PDG_VL
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-0435
  2022-07-04610
  2022-08-04323
  2022-10-041215
  2022-06-04122
  2022-08-0431
  2022-09-0464
  2022-10-0468
  2022-07-04312
  2022-08-0412100
1 ACCEPTED SOLUTION
PDG_VL
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
)

 

View solution in original post

6 REPLIES 6
PDG_VL
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
)

 

v-shex-msft
Community Support
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.

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.

 

CreationDateOtherDateInvoiceDateInvoice_Frequiency_in_monthsAmountID
  6/4/20221221
  7/4/2022352
  7/4/20226103
  7/4/20223124
  8/4/20223235
  8/4/2022316
  8/4/2022121007
  9/4/2022648
  10/4/202212159
  10/4/20226810

 

Expected result
DateAmountInvoiced_Ids
2022-May0-
2022-Jun21
2022-Jul272,3,4
2022-Aug1245,6,7
2022-Sep48
2022-Oct409,10,2,4
2022-Nov245,6
2022-Dec0-
2023-Jan272,3,4
2023-Feb245,6
2023-Mar48
2023-Apr2510,2,4
2023-May245,6
2023-Jun21
2023-Jul272,3,4
2023-Aug1245,6,7

 

 

Thanks

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

1.png
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.

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

PDG_VL
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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

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

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors