Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dustin1232
Frequent Visitor

Yearly Abonnemets to Monthly Abonnements

Hey People,

 

Suppose my data looks like this:

 

Capture2.PNG

 

For now I'm using a python script to get the following: 

 

Capture.PNG

 

I used the following script: 

 

-->

# 'dataset' holds the input data for this script

 

import pandas as pd
from dateutil.relativedelta import *
from datetime import datetime, timedelta
pd.options.mode.chained_assignment = None

 

x = len(dataset)
for i in range(0,x):
if dataset["abotype"][i] == "Yearly":
dataset["price"][i] = dataset["price"][i]/12
new_data = dataset.iloc[i]

for j in range(0,11):
new_data["BuyDateTime"] = pd.to_datetime(new_data["BuyDateTime"])+relativedelta(months=+1)
# new_data["BuyDate"] = pd.to_datetime(new_data["BuyDate"])+relativedelta(months=+1)
dataset = dataset.append(new_data, ignore_index=True, verify_integrity=True)

<--

 

Unfortunately, if I want to process the data by using a data gateway in power BI service, I get an error because of the python code I'm using.
So i guess im stuck doing it via DAX, M. 

 

Any suggestions?

 

Thank you very much for your help!

2 REPLIES 2
v-danhe-msft
Employee
Employee

Hi @dustin1232 ,

Based on my test, you could refer to below stpes:

Create a start column for your row table:

Start Date = EOMONTH('Row table'[BuyDate],-1)+1

1.PNG

Create a middle table:

Middle table = CALENDAR(VALUES('Row table'[Start Date]),VALUES('Row table'[BuyDate]))

2.PNG

Create a new table and column for your result:

New table:

Result Table = SUMMARIZECOLUMNS('Middle table'[Date],'Row table'[BuyDate],'Row table'[BuyDateTime],'Row table'[email],'Row table'[producttype],'Row table'[zip],'Row table'[Price])
Modified price = [Price]/COUNTROWS('Result Table')

Result:

3.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This doesn't work for me.

 

In my "Row Table":

Capture.PNG

 

In my Middle Table:

Capture2.PNG

 

Again my Table, maybe a bit clearer with the date column:

 

Capture3.PNG

 

And the result:

Capture4.PNG

 

 

What I have are monthly and yearly Abonnements: 
IF they are Yearly I want to split them into monthly entries. 

So e.g January 15 Yearly Abonnement has 11 more entries until December 15. 
The conditioning on YEARLY is not that important as I can just create a table with only YEARLY entries.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.