Snkirkland Tracker

Re: Calculating CY revenue based on an even spread of sale value over the period of performance

Snkirkland — Sat, 16 Apr 2022 05:40:13 GMT

I cant get that to work. Can you?

Re: Calculating CY revenue based on an even spread of sale value over the period of performance

Snkirkland — Fri, 15 Apr 2022 15:32:38 GMT

Ashish_Mathur Thank you. Now how do I limit the output/result of Duration = Data[End Date]-Data[Start Date] to include only a count of the days from the period defined by the start and end date columns that are in a specific calendard year. For instance Start = 5/1/2022 and End = 4/30/22. I want the output of this calculated column to tell me the duration of just the days in 2022 or 12/31/22 less 5/1/2022. I do this in Excel like this =MAX(MIN(DatesforCalcs!E$1,[@End])-MAX(DatesforCalcs!D$1,[@Start]),0). Where DatesforCalcs!E$1 = 12/31/22 and DatesforCalcs!D$1 = 12/31/21.

Re: Calculating CY revenue based on an even spread of sale value over the period of performance

Snkirkland — Fri, 15 Apr 2022 04:23:18 GMT

amitchandak Thank you for your quick reply. I think I would like to boil down my question to: how do I add a calculated column that looks at StartDate and EndDate columns and returns the number of days between the start and end date that are in 2022. Can you assist with that?

Calculating CY revenue based on an even spread of sale value over the period of performance

Snkirkland — Wed, 13 Apr 2022 06:16:01 GMT

I am looking for help figuring out how to write a measure that calculates a CY revenue amount from a table that includes a sale amount, sales date and a start and end date which define the period of peformance for each opportunity/row in the table. I also have a calendar table set up in my Pbi file. I need to run reports to summarize how the revenue associated with our sales by year is expected to be recognized by calendar year.

This is the calculation I have been using in Excel that I am trying to replicate in Powerbi. In Excel I have a column for determining each CY's revenue associated with the sale. So assumng this is the CY 2022 revenue coilumn the DatesforCalcs!E$1 would be pointing to a cell that includes = 12/31/22, DatesforCalcs!C$1 would be pointing to a cell that includes = 12/31/21.

@Start refers to the start date colum.

@End refers to the end date colum.

DatesforCalcs!E$1 refers to the last day of the year,

=MAX(MIN(DatesforCalcs!E$1,[@End])-MAX(DatesforCalcs!D$1,[@Start]),0)/(DATEDIF([@Start],[@End],"d"))*[@Value]

I guess alternatively I could add calculated columns to my Powerbi table for each CY but I assume that is a less efficient way to run this?

Thanks so much for anyone who is willing to help me out!