Distribute calculated amount between Quarters of a Year

[Anonymous]

Hi there,

I have a table with the following shape:

ProductName	StartDate (DDMMYYYY)	EndDate (DDMMYYYY)	MonthyFee	Duration_Month
SoftwareLicense1	01.09.2020	31.08.2021	30,00 €	12
SoftwareLicense2	01.10.2020	30.04.2021	55,00 €	7

I would like to calculate the sum of the MonthlyFees for Q3/2020, Q4/2020, Q1/2021 etc.

Do I have to insert a column for each of my desired aggregates or is there a smarter way? I want to prevent to change the Query in a year and add columns for the quarters of 2022.

I added a Date Table as mentioned here --> https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/#:~:text=%2...

...but I struggle to implement a logic to it.

My aim is to build a report where the user can navigate trough each Product and/or Quarter of a Year and see the summed up Values.

So for example:

Period:	ProductName:	Amount
Q3/2021	SoftwareLicence1	600,00 €
....	....	....

My Input (DataTable with each purchased License in a row and the mentioned columns) will be updated on a monthly basis.

Any hint is highly appreciated!

Thank you,

Frederik

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

For your requirement,you need to  split period data into months.

You need to create a function as below first : (create a funtion/ blankquery ,then paste it into  Advanced Editor)

(startdate as date, enddate as date) as table =>
let
 Source = {Number.From(startdate)..Number.From(enddate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Calculated End of Month" = Table.TransformColumns(#"Renamed Columns",{{"Date", Date.EndOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month"),
    #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Original", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

then apply it to "Invoke Custom Function"

 Expand  all tables and  just keep the columns you want , you will get the following result:

Create a calculated column as below:

Period = FORMAT('Table'[Custom.Original.Date],"\Qq yyyy")

The result will show as below:

For more details,please check the attached pbix file and this related thread.

https://community.powerbi.com/t5/Desktop/Splitting-period-data-into-months/m-p/113861 

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

[Anonymous]

Wow, Extremly Helpful! Worked exactly as I imagined!

Thanks a lot!

[lbendlin]

Does your calendar table have a field for Quarter?

Is it possible for a license to start on a day different from the first day of a month, and to end not on the last day of a month?

Do you care about months having different number of days?

[Anonymous]

Hey,

Yes, my calendar has a field for quarter and a lot of more columns:

In reality the licences can start during a month but in the Input file, this will always be standardized to the first of the month so the licences will always start at the first of a month and end at the last day of a month. The duration of a license is between 1 - 36 months.