Trying for dax measure which gives the latest day sales in all columns

[devendrakotaru]

Hi All,

Attached are the tables, as an example of what I am trying to achieve.
If Wed sales are 630 then all columns should show as 630 irrespective of the column.

If Thu sales are 647 then all columns should show as 647 irrespective of the column.

Monday	Tuesday	Wednesday	Thursday	Friday
528	604	630	0	0
630	630	630	0	0

Monday	Tuesday	Wednesday	Thursday	Friday
528	604	630	647	0
647	647	647	647	0

Measure:

Sale =

VAR Totalv =

IF(SELECTEDVALUE('Fact'[Day Column])="Friday" && SUM('Fact'[Sales])>0,5,

IF(SELECTEDVALUE('Fact'[Day Column])="Thursday" && SUM('Fact'[Sales])>0,4,

IF(SELECTEDVALUE('Fact'[Day Column])="Wednesday" && SUM('Fact'[Sales])>0,3,

IF(SELECTEDVALUE('Fact'[Day Column])="Tuesday" && SUM('Fact'[Sales])>0,2,

IF(SELECTEDVALUE('Fact'[Day Column])="Monday" && SUM('Fact'[Sales])>0,1,0)))))

Var Sales = IF(ISBLANK(Totalv),0, CALCULATE(SUM('Fact'[Sales]),FILTER(Day_Column_Index,Day_Column_Index[Index]=Totalv)))

return

Sales

The above DAX is not working as expected, can someone can help me here?

[v-tangjie-msft · Accepted solution]

Hi @devendrakotaru ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Measure = 
var _add=ADDCOLUMNS(ALL('Table'),"Index",SWITCH(TRUE(),[Day Column]="Monday",1,[Day Column]="Tuesday",2,[Day Column]="Wednesday",3,[Day Column]="Thursday",4,5))
var _find=MAXX(FILTER(_add,[Sales]<>0),[Index])
var _relatedvalue=MAXX(FILTER('Table',[Day Column]=MAX([Day Column])),[Sales])
return IF(_relatedvalue<>0,MAXX(FILTER(_add,[Index]=_find),[Sales]),0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[BeaBF · Accepted solution]

@devendrakotaru 

Sale =
VAR SelectedDay = SELECTEDVALUE('Fact'[Day Column])
VAR TotalSales = CALCULATE(SUM('Fact'[Sales]), 'Fact'[Day Column] = SelectedDay)
RETURN
IF(TotalSales > 0, TotalSales, BLANK())

BBF

[v-tangjie-msft · Accepted solution]

Hi @devendrakotaru ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Measure = 
var _add=ADDCOLUMNS(ALL('Table'),"Index",SWITCH(TRUE(),[Day Column]="Monday",1,[Day Column]="Tuesday",2,[Day Column]="Wednesday",3,[Day Column]="Thursday",4,5))
var _find=MAXX(FILTER(_add,[Sales]<>0),[Index])
var _relatedvalue=MAXX(FILTER('Table',[Day Column]=MAX([Day Column])),[Sales])
return IF(_relatedvalue<>0,MAXX(FILTER(_add,[Index]=_find),[Sales]),0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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