Forum Discussion

rbraga's avatar
rbraga
Frequent Visitor
2 years ago
Solved

Compare 2 different dates dynamically

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Greg_Deckler
Community Champion
2 years ago

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's avatar
rbraga
Frequent Visitor
2 years ago

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()
	     )