Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey People,
Suppose my data looks like this:
For now I'm using a python script to get the following:
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!
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
Create a middle table:
Middle table = CALENDAR(VALUES('Row table'[Start Date]),VALUES('Row table'[BuyDate]))
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:
You could also download the pbix file to have a view.
Regards,
Daniel He
This doesn't work for me.
In my "Row Table":
In my Middle Table:
Again my Table, maybe a bit clearer with the date column:
And the result:
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |