Compare 2 different dates dynamically

[rbraga]

Hello everyone, 

I have just started using power BI a few weeks ago and am running onto this very basic issue. 

I have tried researching for this question in the forums but could not find an answer for this specific question. 

I have a sales table and want to compare the sum of sales for 2 different dates by category with a matrix. I want to have 2 slicers, 1 for each date. 

Slicers (Dropdown)
| DATE 1 |    &   | DATE 2 |

After the user has selected the two dates, the matrix will show the sales for the 2 dates by category. 

_____________________________________________

|                 | Date 1 | Date 2 | Difference |

| Type 1     | 123       | 125     |  2              |

| Type 2     | 50         | 25       |  -25          |

.

I know that this can be done using the calculate function, but I cannot get the title of the Date 1 and date 2 to change dynamically neither to get the user to select the data. 

Appreciate any help. 

Thank you! 

[v-tangjie-msft · Accepted solution]

Hi @rbraga ,

Unfortunately power bi desktop does not support two slicers filtering a matrix visual object together. You can create a date slicer that selects two dates and presents date 1 and date 2 as card visual objects.

We can create a date table.

date = CALENDAR(DATE(2023,3,1),DATE(2023,4,6))

We can create measures.

Difference = 
var _a=CALCULATE(MAX('Table'[sales]),FILTER(ALLSELECTED('Table'),'Table'[date]=MINX(ALLSELECTED('date'),'date'[Date]) && 'Table'[type]=MAX('Table'[type])))
var _b=CALCULATE(MAX('Table'[sales]),FILTER(ALLSELECTED('Table'),'Table'[date]=MAXX(ALLSELECTED('date'),'date'[Date]) && 'Table'[type]=MAX('Table'[type])))
var _c=MINX(ALLSELECTED('date'),'date'[Date])
return IF(MAX('Table'[date])=_c,BLANK(),_b-_a)

date1 = MINX(ALLSELECTED('date'),'date'[Date])

date2 = MAXX(ALLSELECTED('date'),'date'[Date]) 

If you would like to suggest  feature improvements, you may  vote the idea and comment here to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

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. 

[Greg_Deckler]

@rbraga I don't think dynamically changing the column header is possible for it to actually show you the chosen dates but would have to see if there is a tricky way of doing it. In general, you would want to use disconnected date tables for your 2 slicers or at least one of them should be disconnected. Then you would use a lookup to display your values using either LOOKUPVALUE or MAXX( FILTER( ... ), ... ).

[rbraga]

Hi Greg,

Thanks for the fast response! What do you mean when you say to use the LOOKUPVALUE or MAXX( FILTER( ... ), ... ) functions? 

I have tried readapting this solution (https://www.youtube.com/watch?v=JVjeisHA6FU) to my problem but I don't have the expertise yet in DAX to make it work:

New total sales =
VAR _Sales = Calculate([total sales]),
	DimDate[year] = SELECTEDVALUE(‘year in use[year]))
RETURN
	IF(
		SELECTEDVALUE(‘year in use’[year] IN {MAX(dimDate[year]), MAX(dimDate[year])-1}
		_Sales,BLANK()
	     )