Switch Selected Value Errors

[Anonymous]

I am attempting to create a Slicer to toggle between the current values and the day over day change. 

The Date Rank column ranks the days from 1 (most recent) descending. The curr_date variable I have created is to use the most recent day (each table in the visualization is only ever filtered to one day) and the prev_date is shifted to the next most recent day by adding 1 as shown below

var curr_date = min(NG_POS_MGR[Date Rank])
var prev_date = (min(NG_POS_MGR[Date Rank]) + 1)
var switchvalue = 
SWITCH(
SELECTEDVALUE('Toggle Table'[ID])
,1, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = curr_date)/10000
,2, (calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = curr_date)/10000) - (calculate(SUM(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = prev_date))/10000
, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = curr_date)/10000
)
return switchvalue

However, when I use this in the visualizations, the current day values always appear properly, but the day over day change that I am trying to calculate do not. I will either get empty tables or I will get the alternate result, which in this case is just the summed value again.

I would greatly appreciate any help on this, thanks

[parry2k]

@Anonymous it should work, how you are trying to visualize the data. Can you return prev_date value from the measure to check if you are getting correct value if yes, then just return calculate expression for prev day and see you are getting value.

[Anonymous]

 

I think this may be my issue, I am getting values but I am getting 1's where I thought I would just be getting  null values. Ideally, this table would just be 2's and null values

[parry2k]

@Anonymous I'm not surely if you resolved your issue or still need help.

[Anonymous]

I am finding that my issue is actually filtering in the visualization. I am filtering using the date column but this seems to be filtering out the values from the previous day, so I am just getting the same value as the sum. I can filter by the date rank column and get the correct values for the difference but I cannot seem to get it to work when using the date column

I have changed my code with the thought that using the date instead of the date rank will yield the correct answer to no avail

Selected Value = 
var curr_date_rank = min(NG_POS_MGR[Date Rank])
var prev_date_rank = (Min(NG_POS_MGR[Date Rank]) + 1)
var curr_date = CALCULATE(min(NG_POS_MGR[Date]), NG_POS_MGR[Date Rank] = curr_date_rank)
var prev_date = CALCULATE(min(NG_POS_MGR[Date]), NG_POS_MGR[Date Rank] = prev_date_rank)
var switchvalue = 
SWITCH(
    SELECTEDVALUE('Toggle Table'[ID])
    ,1, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date] = curr_date)/10000
    ,2, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date] = curr_date)/10000 - calculate(SUM(NG_POS_MGR[Delta]), NG_POS_MGR[Date] = prev_date)/10000
    , 0
)
return switchvalue

[parry2k]

@Anonymous it shoul be straight forward, can you share sample pbix file, you can remove sensitive information before sharing.  

[Anonymous]

Sent!

[v-kelly-msft]

Hi  @Anonymous ,

Can you upload your .pbix file to onedrive business and share the link with me?

Best Regards,
Kelly

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

[parry2k · Accepted solution]

@Anonymous here is the measure provided as solution by email.

Selected Value = 
var curr_date_rank = min(Sheet3[Date Rank])
var prev_date_rank = (Min(Sheet3[Date Rank]) + 1)
var switchvalue = 
SWITCH( SELECTEDVALUE(Query1[ID]),
1, calculate(sum(Sheet3[Delta]), Sheet3[Date Rank] = curr_date_rank)/10000 ,
2, calculate(sum(Sheet3[Delta]), Sheet3[Date Rank] = curr_date_rank)/10000 - calculate(SUM(Sheet3[Delta]), ALL(Sheet3[Date]) ,Sheet3[Date Rank] = prev_date_rank)/10000, 0)
return 
switchvalue