How to calculate Prorata Value for a given year(Club year)

[a4]

Hi ,
I have a table which contains a perday value of Gross Tonnage.I have two others columns startdate and enddate.(For ex: start date is 3/12/2018 and enddate is 3/12/2020).I have a caculated column which looks at the start date and if  startdate is less than 20th feb then year  falls in the previous year and if startdate is more than 20th of feb then year falls in the same year.We call this year as a club year.

Below is my formula for club year:

Actual(Club year) = IF(DATE(quotedetails[_startdate].[Year],2,20)>quotedetails[_startdate].[Date],quotedetails[_startdate].[Year]-1,quotedetails[_startdate].[Year])

I am using this column(Club year) as a slicer in my report.

The startdate and endate is for a particular policy.

I want to calculate the Prorata Gross Tonnage on yearly basis depending upon start date and endate.Suppose if a policy has a start date 2/23/2019 and end date 2/25/2021 the I want to calculate Prorata Gross Tonnage for each year(Club year).

Please refer below screenshot for better understanding.

[v-gizhi-msft]

Hi,

According to your description, i create a sample to test:

Then please try this column:

Club Year = IF('Table'[Date]<DATE(YEAR('Table'[Date]),2,20),YEAR('Table'[Date])-1&"- Club Year",YEAR('Table'[Date])&"- Club Year")

Choose this column and [Gross Tonnage] as a table visual, it shows the total values of each club year:

Hope this helps.

Best Regards,

Giotto Zhi

[a4]

Hi Giotto
Thanks for your reply. There is no extra column of date in my table, it's just the start date and end date.
And a club year logic(20th feb of year to 20th of feb of next year) which is used as a slicer.I need to multiply the perdaygrosstonnage with no.of effective days in the particular club year and show that as a sum for all the policies.

Kind regards
Amit Kumar

[v-gizhi-msft · Accepted solution]

Hi,

I change my original test table to this:

Then try this column:

Club Year = IF('Table'[Start Date]<DATE(YEAR('Table'[Start Date]),2,20),YEAR('Table'[Start Date])-1&"- Club Year",YEAR('Table'[Start Date])&"- Club Year")

It shows:

Then create a measure:

Measure = SUM('Table'[Gross Tonnage])*COUNTROWS('Table')

Choose one club year in slicer, the result shows:

Best Regards,

Giotto Zhi

[Tad17]

Hey @a4 

I don't see the screenshot you mention, but I am currently experiencing wifi problems so that may just be me.

I believe all you need is the SAMEPERIODLASTYEAR function found here: https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

If you need it to interact with your slicer you can use the SELECTEDVALUE function to do that: https://docs.microsoft.com/en-us/dax/selectedvalue-function

Examples: https://powerpivotpro.com/2018/02/using-selectedvalues-capture-power-bi-slicer-selections/

https://stackoverflow.com/questions/50929240/how-to-capture-slicer-value-by-dax-measure

If this helps please kudo.

If this solves your problem please accept it as a solution.