Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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 )
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 )
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
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
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
Thanks,but it still doesn't solve the maien issue: repeat values based on frequency.
I have the solutions though, will post it separately.
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)
)
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |