cancel
Showing results for
Did you mean:
Highlighted
Helper III

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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

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

4 REPLIES 4
Highlighted
Solution Sage

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

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

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.

Highlighted
Community Support

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

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

Highlighted
Helper III

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

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

Highlighted
Community Support

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

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

Announcements

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors