Forum Discussion
Differing values
Hello,
i want to differing values that 1 have in a period, for the next 12 months ahead.
for example:
| Insurance code | Begging date | end data | value (costs) |
| person A | 31/12/2024 | 31/12/2024 | 120 |
| person B | 30/11/2024 | 30/11/2024 | 180 |
What im trying to do is : i want to create 12 lines for each person, 1 for each month (Jan/25, Feb/25, .....until Dec25, with 1/12 of the value that i've, in this case, 120/12 =10e for each month
The same for person B, that should have 15e for each month.
How can i do this using DAX ?
thanks,
JR
You can try in Power Query with the following steps:
- Add a new Column in PowerQuery using this formular pattern: List.Numbers(0, ([Data value]/12)+1)
- Expand this new column using the "Expand to new rows" option. Change the data type here to Whole Number
- You can create a new Date End Column using this formula pattern: Date.AddMonths([Dateend], [NewCol])
- If this works for you, kindly mark as Answer to make it easier for anyone with similar issues to find a solution
- Add a new Column in PowerQuery using this formular pattern: List.Numbers(0, ([Data value]/12)+1)
3 Replies
Must this be a DAX solution or you can work with PowerQuery. Since, it has to do with extra data, PowerQuery might provide a more viable solution.
i can create in power query the subset data with this data. how can i convert 1 line in 12 lines, 1 per each month in power query ?
You can try in Power Query with the following steps:
- Add a new Column in PowerQuery using this formular pattern: List.Numbers(0, ([Data value]/12)+1)
- Expand this new column using the "Expand to new rows" option. Change the data type here to Whole Number
- You can create a new Date End Column using this formula pattern: Date.AddMonths([Dateend], [NewCol])
- If this works for you, kindly mark as Answer to make it easier for anyone with similar issues to find a solution
- Add a new Column in PowerQuery using this formular pattern: List.Numbers(0, ([Data value]/12)+1)
