cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to calculate the date difference between a existing column with date slicer "selected value"

Hi ALL, 

 

I have a table with two date columns "ValueDate" and "EOMDate" (end of month date), I have taken the EOMDate in a date slicer and also able to capture the value selected from the slicer using below code with a Measure as below.

ValueSelected = CALCULATE(
MAX(Remittance[Remittance.EOMDate]),

ALLSELECTED(Remittance[Remittance.EOMDate]))


but I am unable to compare the measure "ValueSelected" with column "ValueDate". 

I tried this first 

AgeMeasure = DATEDIFF(Remittance[Remittance.ValueDate],Remittance[ValueSelected],DAY)

I got the below error

"A single value for column 'Remittance.ValueDate' in table 'Remittance' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I tired the below as an alternative approach, which is throughing the same error , any advise will be much helpful.. Hearty thanks. 

AgeMeasure = 
VAR MinVDate = MIN(Remittance[Remittance.ValueDate])
VAR MinVVD = DATEDIFF(MinVDate,Remittance[Remittance.ValueDate],DAY) // Getting the error Here 
VAR SVMVD = DATEDIFF(MinVDate,[ValueSelected],DAY)
RETURN
SVMVD - MinVVD
1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to calculate the date difference between a existing column with date slicer "selected v

It's not clear to me what you are trying to do here.  What a slicer does is filter your table.  The best way to understand this is to load your data into Excel, convert it to a table and then place a filter on the EOM column for the date you want.  This is what a slicer does.  Now that the table is filtered, what do you want to do?  The table has a filter on it so you are limited in what you can do to the filtered copy of the table.

 

So, still not really knowing what you want to do, it is likely that you will need 1 or 2 disconnected slicers that are on a table of dates. Click on this new slicer to harvest the value that the user selects.  This way you get the input without placing a filter on the table.

 

If you can provide some more information about the logic of what you are trying to acheive I could possibly help some more



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Re: How to calculate the date difference between a existing column with date slicer "selected v

Example Data

EOMDateValueDateSelectedDate
12/31/201212/06/201210/31/2016
02/28/201302/21/201310/31/2016
08/31/201408/18/201410/31/2016
09/30/201409/14/201410/31/2016

 

Expected Output

EOMDateValueDateSelectedDateAgeValue
12/31/201212/06/201210/31/20161425
02/28/201302/21/201310/31/20161348
08/31/201408/18/201410/31/2016805
09/30/201409/14/201410/31/2016778

 

Note:
ValueDate: >> Column from the Source (SSAS Tabular model)

SelectedDate / ValueSelected : >> a measure calculated based on the date slicer built on "EOMDate" column

Anonymous
Not applicable

Re: How to calculate the date difference between a existing column with date slicer "selected v

@MattAllington, could you please help me on this  ..

Anonymous
Not applicable

Re: How to calculate the date difference between a existing column with date slicer "selected v

@MattAllington, @Greg_Decklercould you please help me on this  ..

Re: How to calculate the date difference between a existing column with date slicer "selected v

It's not clear to me what you are trying to do here.  What a slicer does is filter your table.  The best way to understand this is to load your data into Excel, convert it to a table and then place a filter on the EOM column for the date you want.  This is what a slicer does.  Now that the table is filtered, what do you want to do?  The table has a filter on it so you are limited in what you can do to the filtered copy of the table.

 

So, still not really knowing what you want to do, it is likely that you will need 1 or 2 disconnected slicers that are on a table of dates. Click on this new slicer to harvest the value that the user selects.  This way you get the input without placing a filter on the table.

 

If you can provide some more information about the logic of what you are trying to acheive I could possibly help some more



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Microsoft v-jiascu-msft
Microsoft

Re: How to calculate the date difference between a existing column with date slicer "selected v

Hi @Anonymous,

 

Actually, you don't want to filter the data. You'd like to indicate the date instead. I would suggest you create a new table [EOMDate] that will provide the field of the slicer. 

Please download the demo from the attachment.

SelectedValue = SELECTEDVALUE('Table'[EOMDate])
Measure =
DATEDIFF ( MIN ( 'Table1'[ValueDate] ), [SelectedValue], DAY )

How-to-calculate-the-date-difference-between-a-existing-column-with-date-slicer-selected

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: How to calculate the date difference between a existing column with date slicer "selected v

Hi @v-jiascu-msft

 

Thanks for your Great time..  it is working fine with "List" and "Dropdown" type of date slicer, but for my requirement I am using "Before" type of slicer where this "SelectedValue" is not working.. 

 

unfortunately, I need to filter the data to get whatever data available Prior to the date (EOMDate) as per the selection

 

any help is much & more appreciated.. 

 

Thanks, 

Prabhu

Anonymous
Not applicable

Re: How to calculate the date difference between a existing column with date slicer "selected v

Hi @MattAllington,

 

Thanks for your kind response, 

 

all I want to achieve is, 

1) Need to filter the table based on the slicer selection (condition is to get the data prior to the date selected)

2) Need to find the date difference (age value ) from the date selected through "Before" date slicer. 

 

hope this clarifies your doubts.. 

 

Thanks, 

Prabhu

Microsoft v-jiascu-msft
Microsoft

Re: How to calculate the date difference between a existing column with date slicer "selected v

Hi @Anonymous,

 

It's my pleasure. The measure could be useful in many scenarios. Please try it out.

 

SelectedValue = Max('Table'[EOMDate])

 

Best Regards,
Dale

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors