Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |