Calculating monthly or yearly costs based on single "monthly cost definition"

[Fin-DuS]

Hi Power BI Pros!

I have a problem and cant figure out the correct solution.

I have a table called Software that looks like this:

softwareId	name	initialPrice	monthlyPrice	purchaseDate	licenseValidUntil	category
1	O365 License	100	350	1.1.2021	31.7.2021	User
2	Contoso X		200	17.2.2021		User
3	XYZ	500		1.9.2021	30.8.2022	Internal
4	ABC		500			Datacenter
5	ETC	1000	50	20.5.2020	28.4.2021	User

What I needed now was to create a view based on these definitions, so I created a dax calendar table and the first thought was for each day calculate the cost (monthly costs always at the first day of the month). But then I just had a sum per day and I was not able to place any legend on these visuals for category i.e.

Then I created a measure that calculates that and in the visual I just show the specific month so the measure should get applied correctly.

Here are the measures that I wrote:

InitialPriceSumPerMonth = CALCULATE(
    SUM('software'[initialPrice]),
    FILTER('software',DATE(YEAR('software'[purchaseDate]),MONTH('software'[purchaseDate]),1) = MIN('software cost (calendar)'[Date]))
)

Here I check all the initial costs of the current filtered month using the calendar table.

MonthlyPriceSumPerMonth = 
    CALCULATE(
        sum('software'[monthlyPrice]),
        filter('software', 
            and(
                or(
                    date(year('software'[purchaseDate]),month('software'[purchaseDate]),1) <= min('software cost (calendar)'[Date]),
                    isblank('software'[purchaseDate])
                ),
                or(
                    date(year('software'[licenseValidUntil]),month('software'[licenseValidUntil]),1) >= date(year(max('software cost (calendar)'[Date])),month(max('software cost (calendar)'[Date])),1),
                    isblank('software'[licenseValidUntil])
                )
            )
        )
    )

With this measure I sum all monthy costs of all softwares that are >= purchase date month and <=  licenseValidUntil.

TotalPricePerMonth = [InitialPriceSumPerMonth] + [MonthlyPriceSumPerMonth]

 Thats just the sum of both to show it in different visuals.

But now other people want to see this data summed quarterly or yearly.

I know that with a lot more calculation it should be possible to archive these results for years and quarters as well but I am wondering if there isnt a better way to do it.

Does somebody has an idea or had the same situation? Thank you!

[Fin-DuS · Accepted solution]

Thanks for the advice @parry2k , luckily I did some more research and came accross this post

https://community.powerbi.com/t5/Desktop/Splitting-Values-between-months-of-two-dates/td-p/180075

and that pointed me into the right direction.

For reference, here is the DAX that I used now:

Creating a new calendar table:

software cost (calendar) = 
var s = date(2016,1,1)
var e = now()
return CALENDAR(date(year(s),month(s),1),date(year(e),month(e+1),1))

And then crossjoin to the software table:

software cost (history) = 
SUMMARIZE(
    FILTER(
        CROSSJOIN('software','software cost (calendar)'), // cross joining the software with the calendar table (to iterate the months where a software was active
        (
               'software cost (calendar)'[Date] >= date(year('software'[purchaseDate]),month('software'[purchaseDate]),1) // join all the dates after the software was purchased
            || isblank('software'[purchaseDate]) // or join if there is no purchase date (software have been there for ever and all time (so lets use the first possible date from the calendar table)
        )
        && (
               'software cost (calendar)'[Date] <= date(year('software'[licenseValidUntil]),month('software'[licenseValidUntil]),1)  // join all the dates untill software is valid
           || isblank('software'[licenseValidUntil]) // or join to the last possible date because its never be valid (set to null)
        )
        && (
               'software cost (calendar)'[Date].[Tag] = 1 // join only the first month (we only have monthly, not daily prices
           || 'software cost (calendar)'[Date] = 'software'[purchaseDate] // or join if we are at the purchase date
        ) 
    ),
'software'[softwareId],'software cost (calendar)'[Date] // show only needed columns
)

And with that data I was able to create the needed columns and created a relation between software and software history.

[parry2k]

@Fin-DuS it will be easier if you throw a sample data and expected output. Very hard to follow the whole post. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490