cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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
Community Support

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

Hi,

 

I change my original test table to this:

3.PNG

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:

6.PNG

Then create a measure:

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

Choose one club year in slicer, the result shows:

5.PNG

 

Best Regards,

Giotto Zhi

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
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

 

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.

Highlighted
Community Support
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:

1.PNG

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:

2.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

Highlighted
Helper III
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
Community Support

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

Hi,

 

I change my original test table to this:

3.PNG

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:

6.PNG

Then create a measure:

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

Choose one club year in slicer, the result shows:

5.PNG

 

Best Regards,

Giotto Zhi

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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