Need to pass a slicer selection value to the calculated column

[Anonymous]

Hi All,

        I have a calcuated column like below which i am using to get the count of days between two dates [Submitted_on] and [Approved_on]. Here my requirement is If the [Approved_on] date is blank and when i select a year in year slicer below calculated column count should change based my year selection. It should calculate the count between submitted on date and the year end date of the selected slicer year if the [Approved_on] date is blank as said before.

   For Example If i select 2021 in the slicer and the [submitted on] date is 20-12-2020 and the [Approved_on] date is blank the count shoul start from 20-12-2020 and end in 31-12-2021 as there is no [Approved_on]. The coumn should be 11 days in 2020 + 365 days in 2021. Help me on this. Thanks in advance.

   For now i have used TODAY() to get the result which is not correct in this requirement.

WorkDays Overall =

VAR approved_on1=if (([State]<>"Deferred" && [State]<>"Duplicate" && [State]<>"Cancelled") || [Special Condition] ,if(isblank([Approved_on]),TODAY(),[Approved_on]),blank())

RETURN

if([Submitted_on]<>approved_on1,if (isblank(approved_on1),blank(),

COUNTROWS (

FILTER (

ADDCOLUMNS ( CALENDAR ([Submitted_on], approved_on1) , "Day of Week", WEEKDAY ( [Date], 1 ) ),

[Day of Week] <> 1

&& [Day of Week] <> 7

)

)),1)

[v-kelly-msft]

Hi  @Anonymous ,

Suppose your table is like below:

Then create a measure as below:

Measure = 
var _submitted=SELECTEDVALUE('Table'[Submitted_on])
var _selected=SELECTEDVALUE('Slicer table'[Year])
var _newapprove=DATE(_selected,12,31)
var _approve=MAX('Table'[Approved_on])
Return
IF(_approve=BLANK(),DATEDIFF(_submitted,_newapprove,DAY),DATEDIFF(_submitted,_approve,DAY))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous]

@v-kelly-msft Thank you veru much for your quick responce. The measure that you have created is working as expected but i need a calculated column in the place of that measue. Why because using that datediff count i need to create one more calculated column as below.

day_diff_20_currentY = Switch ( True(),

[DateCount] < 20,"< 20 bus. days",

[DateCount] >= 20 && [DateCount] <= 40 ,"20 to 40 bus. days",

[DateCount] >= 41 && [DateCount] <= 60, "41 to 60 bus. days",

[DateCount] > 60,"> 60 bus. days",BLANK())

as a measure the above query is working fine but as a clculated column its not working as expected.

Based on the above date count segmentaion i need to find the count of IDs for each segmentation as shown in the below image.

If i bring the measure([DateCount]) to a Table visual that i have created with reference to your measure its working fine.

But my requirement is to show it in a clustered column chart. I have tried it by creating a calculated column[C_day_diff_20_currentY] on top the [DateCount] measure. Which is not working as the above measure[day_diff_20_currentY], you can check it in the visual for 2017 values.

And the Year Slicer is working fine for the Table but not working for the clustered column chart. Can you please help to how to achieve this requirement.

My exact requirement is to show ID count in Values and the Segmentation measure [day_diff_20_currentY] on the X axis of the clustered column chart and when i select the year slicer it should change the values accordingly. Thanks in advance.

[v-kelly-msft]

Hi  @Anonymous ,

Sorry for the late reply!

Could you pls share your .pbix file for test?Remember to remove the confidential information.

Best Regards,
Kelly

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

[Anonymous]

https://1drv.ms/u/s!AnQdQ-WoJrBKafuwbX_HKWlG_lc?e=h5QQ96 

Hi @v-kelly-msft sorry its took some time for me to get the test file above is the link to download it.

I hope you understand my requirement. The measure that you have created is working fine but i want show segmentations on that measure as i explaned in the last reply. And i need to add two more conditions here the days count should be only for week days need to eliminate SAT and SUN and when ever i get negative value for the count it should be blank. Thanks in advance.

[v-kelly-msft]

Hi  @Anonymous ,

Sorry for the late reply.

Firstly you cant directly convert the measure to column,as your selection is dynamic,if you use column,it cant be dynamic.

Create a dim table as below:

Then create 2 measures as below:

measure_days_diff_20 = Switch ( True(),
[DateCount] >= 0 && [DateCount] < 20,"< 20 bus. days",
[DateCount] >= 20 && [DateCount] <= 40 ,"20 to 40 bus. days",
[DateCount] >= 41 && [DateCount] <= 60, "41 to 60 bus. days",
[DateCount] > 60,"> 60 bus. days")

Measure = 
SWITCH(SELECTEDVALUE('Dim table'[Column]),
"< 20 bus. Days",CALCULATE(COUNT('cr_test (2)'[id]),FILTER(ALL('cr_test (2)'),[measure_days_diff_20]="< 20 bus. Days")),
"20 to 40 bus. Days",CALCULATE(COUNT('cr_test (2)'[id]),FILTER(ALL('cr_test (2)'),[measure_days_diff_20]="20 to 40 bus. Days")),
"41 to 60 bus. Days",CALCULATE(COUNT('cr_test (2)'[id]),FILTER(ALL('cr_test (2)'),[measure_days_diff_20]="41 to 60 bus. Days")),
"> 60 bus. Days",CALCULATE(COUNT('cr_test (2)'[id]),FILTER(ALL('cr_test (2)'),[measure_days_diff_20]="> 60 bus. Days")))

And you will see:

For the related .pbix file is attached.

Best Regards,
Kelly

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

[Anonymous]

Hi @v-kelly-msft thanks for your solution it works for me. But in the "DateCount" measure i need to eliminate Weekends like Day 1(Sun) and Day 7(Sat). And i need to show 0 for -ve values. Is it possible to do it in the below measure.

DateCount =

var _submitted=SELECTEDVALUE('cr_test (2)'[Submitted_on])

var _selected=SELECTEDVALUE(Year_Slicer[Year])

var _newapprove=IF(_selected=YEAR(TODAY()),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),DATE(_selected,12,31))

var _approve=MAX('cr_test (2)'[Approved On 1])

var _DateCount=IF(_approve=BLANK(),DATEDIFF(_submitted,_newapprove,DAY),DATEDIFF(_submitted,_approve,DAY))

Return

_DateCount

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First create a calendar table as below:

Date = CALENDAR(MIN('cr_test (2)'[Approved On 1]),DATE(YEAR(TODAY()),12,31))

And a column:

check = 
var _weekday=WEEKDAY('Date'[Date],1)
Return
IF(_weekday=1||_weekday=7,1,BLANK())

Then modify [Datecount]as below:

DateCount = 
var _submitted=SELECTEDVALUE('cr_test (2)'[Submitted_on])
var _selected=SELECTEDVALUE(Year_Slicer[Year])
var _newapprove=IF(_selected=YEAR(TODAY()),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),DATE(_selected,12,31))
var _approve=MAX('cr_test (2)'[Approved On 1])
var _weekdays=IF(_approve=BLANK(),SUMX(FILTER(ALL('Date'),'Date'[Date]>=_submitted&&'Date'[Date]<=_newapprove),'Date'[check]),SUMX(FILTER(ALL('Date'),'Date'[Date]>=_submitted&&'Date'[Date]<=_approve),'Date'[check]))
var _DateCount=IF(_approve=BLANK(),DATEDIFF(_submitted,_newapprove,DAY)-_weekdays,DATEDIFF(_submitted,_approve,DAY)-_weekdays)
Return
_DateCount

For the updated .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous]

@v-kelly-msft Thank you very much for the solution this complets my requirement. Thanks again for your time and follw-up.

[v-kelly-msft]

Hi @Anonymous ,

It's my pleasure.

Best Regards,
Kelly

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

[amitchandak]

@Anonymous , You can not use slicer value in a calculated column. You can only use that in measure

You can use measure like SUMX , MINX and do line level calculations