Dashbord Chart Selection Date to drive Sales YTD and Sales LY YTD


#1

Is there a DAX formula to allow someone to select a date on one chart (Dates and Sales) within a dashboard and have all others dynamically update for such things as Sales YTD, Sales YTD last year etc, hopefully the attached video explains it in more detail

Thanks for any assistance

Eamonn


#2

Eamonn,

There is no single DAX Measure to do this each Model is different. This would require a number of Time Intelligence calculations to see the data you want to display i.e. Sales YTD; Sales YTD Last Year; etc.

Once these DAX Measures are in place the Report will filter as you want.

The key is to get the model setup correctly and you relationships in place.

Reference Sam’s Ultimate Beginner’s Guide to Power BI and the Guide to DAX. Then move on to the Time Intelligence Calculations.

Guy


#3

Thanks Guy for the quick response,

The model is set up correctly and the “limited number” of time intelligence measures are in place. The challenge I believe is swapping out the regular Measures format of say
Sales YTD = Calculate ([Total Sales], DatesYTD(Dates[Date])
with something that references the date selection of the first chart in the video
Sales YTD = Calculate ([Total Sales], DatesYTD(some sort of Dates referencing the date just selected in chart 1)


#4

Eamonn,

This is hard to do without any reference.

Is it possible to post a copy of the pbix file so I can take a look at what you have.

Guy


#5

Thanks Guy here you go

WIP_GSSales.pbix (2.2 MB)


#6

Eamonn,

Thanx for the file. Let me look at this and get back to you.

Guy


#7

Eamonn,

Your model is correct as it stands. The request you said you needed for other date selections isn’t needed as everything is well defined in the Measures you wrote.

The model doesn’t work correctly because of a couple of minor things:

  1. I removed the 2 page level filters as they are limiting the data displayed for other years/months. With the filters removed the model filters with your slicers.
  2. I deleted and re-created the MonthName slicer as the one that was there kept selecting all the months and was preventing the selection of any month without going through the process of de-selecting all the months. Why this was happening I’m unsure but re-creating the slicer fixed it.

What you have done is good. Try making the changes I suggested and test the model to see if this is what you need/expect to see.

If not detail what is still missing.

Guy