Days since the start of a defined period

[LooneyOMooney]

I have a date slicer which enables me to filter records in a table visualisation.

I would like a new column which shows me how many days have elapsed between the date associated with each record and the earliest date chosen on the slicer.

I woulld also like to know how many days are in the chosen slicer period.

Essentially these will be used to weight values from the chosen records.  I need to know, for example, that the slicer period is 100 days and that a particular record's date is 50 days after the start of the period chosen in the slicer.  The record's value would be weighted to reflect the fact that it occuered halfway thrugh the period.  This weighting would apply to all records in the period. 

[v-robertq-msft · Accepted solution]

Hi, @LooneyOMooney 

According to your description and sample picture, it seems you are facing the problem that the total row of the measure displayed incorrectly, you can try to create a new measure to replace the original measure in the chart:

Weighted cashflows_new =

var _new=SUMMARIZE('Table','Table'[Date], 'Table'[Category],"_value",[Weighted cashflows])

return IF(HASONEVALUE('Table'[Date]),[Weighted cashflows],SUMX(_new,[_value]))

Then you can go to the table chart to replace the measure [Weighted cashflows] with this one, and you can check if you can get what you want.

If this measure can’t help you to get what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Some related links:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376 

https://community.powerbi.com/t5/Desktop/How-to-show-total-row-as-sum-of-distinct-count/td-p/1416468 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, @LooneyOMooney 

According to your description, I think I can create some measures to place them into charts to achieve your requirement:

This is my test data:

First I created a date table for the date slicer:

Date = CALENDAR(DATE(2021,1,1),TODAY())

To get the dates between earliest date in the slicer and the dates in the record, you can create a measure like this:

Dates between earliest date =

var _mindate=MINX(ALLSELECTED('Date'),[Date])

return

DATEDIFF(_mindate,MAX('Table'[Date]),DAY)

Then place them like this:

To know how many days are in the chosen slicer period, you can create a mesure and place it like this:

days in the chosen slicer period =

DATEDIFF(MIN('Date'[Date]),MAX('Date'[Date]),DAY)

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LooneyOMooney]

Thanks,

I came up with something similar myself but am having trouble with the second step of my question - namely the weighting of values in each record based on the number derived in this measure.

The Wjt measure is used to weight the value that is held in the Amount_USD column.  If the value occurs near the beginning of the period then it is given a heavier weight.  If it occurs near the end then it's given a much smaller weight.  Those 'weighted cashflows' in the far right column look correct but I need to caputre the total value in that column.  However, the total at the bottom of the column shows $101,905 which clearly isn't the sum of the column.  It looks to me like it's the product of the sum in the amount_USD column ($6,114,313) multiplied by the 0.166666667 value in the sum of the Wjt column which isn't what I want.  

[Ashish_Mathur]

Hi,

Assuming the Date slicer was built from the Calendar Table and the Date in your Table visual are from the Sales Table, write this measure

Diff = max(data[sales date])-min(calendar[date])

Hope this helps.

[daxer-almighty]

Are you talking about a measure or a calculated column? A calculated column can't be dynamic, so either way you need to be talking about a measure. Just checking if this is what you really have in mind.

In order to solve your puzzle, you have to show/tell us a little more than just the description above.