Prorate revenue across years not displayed in visualization.

[PAA]

Hi All

Any advice is much appreciated! 

My issue is I have a proted revenue measure which prorates revenue from subscriptions. The measure is working correctly in calculation mode, but when visualized in a matrix or table the prorates are only showing correctly for the first year, in which the subscription is valid, but not for the second year etc.

Ex. Take a  6 month subscription which starts october 2024 and ends march in 2025.  For said susbscription the visualization will display the prorate revenue for 2024, but not the prorate for 2025(!) 

Why does the prorate for 2025 not show in the visualization, and what should be altered in the measure to have the prorate for 2025 displayed?

Best regards 
PAA


Here is my measure:

ProratedRevenue =

VAR StartDatePerVisual = MIN(Guest[StartDate])

VAR EndDatePerVisual = MAX(Guest[EndDate])

VAR RevPerDay = MAX(Guest[RevRate])

-- Ensure proper relationship for StartDate and EndDate

VAR RevForStartDate =

    CALCULATE(

        SUMX(Guest, Guest[RevRate]),

        USERELATIONSHIP(Guest[StartDate], DateG[Date])

    )

VAR RevForEndDate =

    CALCULATE(

        SUMX(Guest, Guest[RevRate]),

        USERELATIONSHIP(Guest[EndDate], DateG[Date])

    )

-- Helper table to get the year split between years

VAR FilteredYears =

    DISTINCT(

        SELECTCOLUMNS(

            CALCULATETABLE(

                DateG,

                DateG[Date] >= StartDatePerVisual,

                DateG[Date] <= EndDatePerVisual

            ),

            "Year", YEAR(DateG[Date])

        )

    )

-- Calculate prorated revenue for each year using SUMX

VAR RevenuePerYear =

    SUMX(

        FilteredYears,

        VAR CurrentYear = [Year]

        VAR YearStart = DATE(CurrentYear, 1, 1)

        VAR YearEnd = DATE(CurrentYear, 12, 31)

        -- Calculate the start and end date of the overlap between the subscription and the current year

        VAR OverlapStart = MAX(StartDatePerVisual, YearStart)

        VAR OverlapEnd = MIN(EndDatePerVisual, YearEnd)

        -- Calculate the number of days in the current year for this subscription

        VAR DaysInYear = DATEDIFF(OverlapStart, OverlapEnd, DAY) + 1

        -- If there is an overlap, calculate the prorated revenue

        RETURN

            IF(DaysInYear > 0, DaysInYear * RevPerDay, 0)

    )

RETURN

    RevenuePerYear

[Anonymous]

Hi @PAA ,

Base on your description, it seems that the revenue across the years didn't display in the visual. Could you please provide some sample data (exclude sensitive data) in the table 'Guest' and 'DateG'  with Text format and your expected result with backend logic base on the provided sample data? By the way, is there any relationship between the table 'Guest' and 'DateG' ? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[PAA]

Hi 

v-yiruan-msft

Preview

Thank you very much for reply. 

DateG(Date) is related to Gust[StartDate]. 'DateG' is a simple Calendar table with a Date Column and a Year column. Dates (short date format) running from 01.01.2026 to 31.12.2030, and Years - Gerenral format -  running from 2016 to 2030. The 'Guest' table is as follows (small sample): 

Guest[Indeks]	Guest[StartDate]	Guest[EndDate]	Guest[BillCycle]	Guest[RevRate]	Guest[InvoiceDate]	Guest[BillCycleVisual]	Guest[DebugStartYear]	Guest[DebugYearInvoice]
397303	Tue, 05 Nov 2024 00:00:00	Sun, 04 May 2025 00:00:00	6	0.308386791136612	Fri, 06 Sep 2024 00:00:00	6M	2024	2024
561853	Fri, 13 Oct 2023 00:00:00	Fri, 12 Apr 2024 00:00:00	6	11.375.623.697.704.900	Mon, 14 Aug 2023 00:00:00	6M	2023	2023
355123	Fri, 14 Feb 2025 00:00:00	Fri, 13 Feb 2026 00:00:00	12	0.0902918406520548	Sat, 16 Nov 2024 00:00:00	12M	2025	2024

[Anonymous]

Hi @PAA ,

I load the above sample data and apply the same formula with yours, get the following values(see the attachment). Is that your expected result? Could you please provide the expected result base on your provided sample data? Thank you.

Best Regards

[bhanu_gautam]

@PAA , The issue with your measure seems to be related to how the StartDatePerVisual and EndDatePerVisual are being calculated and used within the measure. Specifically, the measure might not be correctly handling the prorated revenue calculation across multiple years.

DAX
ProratedRevenue =
VAR StartDatePerVisual = MIN(Guest[StartDate])
VAR EndDatePerVisual = MAX(Guest[EndDate])
VAR RevPerDay = MAX(Guest[RevRate])

-- Helper table to get the year split between years
VAR FilteredYears =
DISTINCT(
SELECTCOLUMNS(
CALCULATETABLE(
DateG,
DateG[Date] >= StartDatePerVisual,
DateG[Date] <= EndDatePerVisual
),
"Year", YEAR(DateG[Date])
)
)

-- Calculate prorated revenue for each year using SUMX
VAR RevenuePerYear =
SUMX(
FilteredYears,
VAR CurrentYear = [Year]
VAR YearStart = DATE(CurrentYear, 1, 1)
VAR YearEnd = DATE(CurrentYear, 12, 31)

-- Calculate the start and end date of the overlap between the subscription and the current year
VAR OverlapStart = MAX(StartDatePerVisual, YearStart)
VAR OverlapEnd = MIN(EndDatePerVisual, YearEnd)

-- Calculate the number of days in the current year for this subscription
VAR DaysInYear = DATEDIFF(OverlapStart, OverlapEnd, DAY) + 1

-- If there is an overlap, calculate the prorated revenue
RETURN
IF(DaysInYear > 0, DaysInYear * RevPerDay, 0)
)

RETURN
RevenuePerYear

[PAA]

Hi Bhanu_gautam

Thanks for your advice. It is not the fix but I am still loooking into it (i.e. the measure is handling the prorated revenue calculation correctly across multiple years, however the second year term of a prorated revenu from a subscription crossing january first is not shwon in the matrix visualisation, but it is included in total).  Thank you very much.