User input for the start date

[thanish]

Below is a scenario I have in MMDDYYYY format

Start Date	End Date	Unit_Price	Quantity	TotalPrice
1/1/2015	6/1/2015	200	1	1000

Consider the above is the sample of rental price of the product for the given period. Unit_price is the rental price for a month. From the start date and end date it constitutes to 5 months which would give the Total price/revenue of 1000 to the compay.

Now my company generates report monthly/many months together to find out how much a particular product has been generating. 

I can create a start date filter using the filter chart and bar chart with months/Date in x-axis and unit_price in y-axis.

If I click on January in the filter my bar chart will map to the Start Date column for x-axis and Unit_Price for Y-axis. But what would be if I click for the months February or March or April or May since those months are not in either Start Date or End Date column ?

Sometimes my management would like to know the revenue for months from Feb to May (4 months). In that case the total revenue should be 800, Is there a way in PowerBI to do such calculations ? 

Is there a way user can give the date(end date) and then calculate the period subtracting from the start date ?

[Anonymous · Accepted solution]

Hi @thanish,

According to your description, you want to use slicer choose an end date instead of filter data, right?

If it is a case, you can follow below steps:

Data table:

 

Calendar table: (not related to data table)
DateTable = CALENDAR(MIN(Sheet1[Start Date]),MAX(Sheet1[End Date]))

 

Measures.

Use to get the select date:
Selected End data =
var currentEnddate=MAX(Sheet1[End Date])
return
if(HASONEVALUE(DateTable[Date]),VALUES(DateTable[Date]),currentEnddate)

calculate the data range and the total price:
Total Price =
var unit=MAX(Sheet1[Unit_Price])
var qty=MAX(Sheet1[Quantity])
var startDate= MAX(Sheet1[Start Date])
var endDate= MAX(Sheet1[End Date])
return
CALCULATE(if(AND([Selected End data]<=endDate,[Selected End data]>=startDate), DATEDIFF(startDate,[Selected End data],MONTH),if([Selected End data]<startDate,0,DATEDIFF(startDate,endDate,MONTH))) * unit * qty,ALLSELECTED(Sheet1))

Create visuals.

Slicer:

 

Table visual:
 

Result:

 

Comment of formula:

if(AND([Selected End data]<=endDate,[Selected End data]>=startDate), DATEDIFF(startDate,[Selected End data],MONTH),if([Selected End data]<startDate,0,DATEDIFF(startDate,endDate,MONTH)))

if “selected date” is between “startdate” and “enddate”, get the date range to calculate. If “selected date” less than “start date”, set date range to 0. If “selected date” greater than “enddate” set the date range to old range.

If above is not help, please feel free to post.

Regards,
Xiaoxin Sheng

[BhaveshPatel]

So as per my understaning you would like to create a running total for the particular period. 

If that is the case, Please refer to the below blog post.

https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/

Thanks & Regards,

Bhavesh