cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to get monthly sum from when there is an effective date and expiration date?

I need to get a monthly sum for the next three years. However, there is an effective date which is the start date and an expiration date. I need the total to populate according to the date range.

 

This is the current data:

 

Part #    Monthly Rate    EFFDT            EXPDT
01-02-1303    17.00    11/27/2018      --
01-02-3000    1,029.60    2/4/2019    5/4/2019

 

I would like to create a table that has a 12 month view and shows the monthly rates for the next three years.  The first part number does not have an expiration date. The second part number has a range. Below is how I would like the table to look like when completed.

 

Part #               Year        Jan            Feb             March            

01-02-1303     2019        17            17                17 

                        2020        17            17                17 

                        2021        17            17                 17 

 

01-02-3000      2019         0            1029.60    1029.60

                         2020         0               0                 0

                         2021         0               0                 0

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: How to get monthly sum from when there is an effective date and expiration date?

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Highlighted
Super User IX
Super User IX

Re: How to get monthly sum from when there is an effective date and expiration date?


Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

Perhaps also:

 

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver II
Resolver II

Re: How to get monthly sum from when there is an effective date and expiration date?

I don't think your current data structure supports this. However, I think you can quickly create a date table with the three years you're interested in:

 

Date = CALENDAR(DATE(2019,1,1),DATE(2021,12,31))
 
And then cross join that with your current dataset (I put the two rows of data you provided into excel):
 
Cross Join = CROSSJOIN('Date',Sheet1)
 
Next, I created a "Rate" field to leverage your criteria:
 
Rate = IF ( AND('Cross Join'[Date] <= IF(ISBLANK('Cross Join'[EXPDT]),DATE(2099,1,1),'Cross Join'[EXPDT]), 'Cross Join'[Date] >= 'Cross Join'[EFFDT]),'Cross Join'[Monthly Rate],0)
 
I then created a "matrix" visual with Part # and Year of date on the rows, and Month of date on the columns. I filtered on the months you're interested (Jan- March). For the measure, I used the MAX(Rate) and the output looks like what you're expecting:
Month Sum EFFDT EXPDT.PNG
 
Not sure if this is the most efficient way to do this, but it's what I came up with. Let me know if this is helpful or works for you.
 
Thanks,
Ben
 
 
Highlighted
Super User V
Super User V

Re: How to get monthly sum from when there is an effective date and expiration date?

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper I
Helper I

Re: How to get monthly sum from when there is an effective date and expiration date?

Hi Ashish,

 

Thank you so much for your help. The file worked and I am now getting the results. The only issue I am having is that the month will not show in order. I went through your file and repeated the steps but I am afraid I am missing something. This is the result after I applied the steps shown within your file. Please let me know how to show the months in order. Thanks again.

 

 Updated Table.png

 

Highlighted
Super User V
Super User V

Re: How to get monthly sum from when there is an effective date and expiration date?

Hi,

 

If my reply helped, please mark it as Answer.  Create a relationshipship from the Month column of the Calendar Table to the Month column of the Month Order Table.  In the Calendar Table, write the RELATED() function.  Click on any cell in the month column of the Calendar Table and go to Sort By > Month Order.  The months in the visual will now appear properly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper I
Helper I

Re: How to get monthly sum from when there is an effective date and expiration date?

Thank you !! It worked. I did not sort it which was causing the issue. Again, thank you so much for your help!

Highlighted
Super User V
Super User V

Re: How to get monthly sum from when there is an effective date and expiration date?

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors