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

eamonn · January 21, 2019, 6:06pm

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

GuyJohnson · January 21, 2019, 6:21pm

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

eamonn · January 21, 2019, 7:17pm

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)

GuyJohnson · January 21, 2019, 7:28pm

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

eamonn · January 21, 2019, 10:35pm

Thanks Guy here you go

WIP_GSSales.pbix (2.2 MB)

GuyJohnson · January 22, 2019, 11:56am

Eamonn,

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

Guy

GuyJohnson · January 22, 2019, 2:46pm

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:

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.
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