Date Slicer control two visualizations

[nandvroe]

I have a two date slicers  based on a calendar table one for Fiscal Month and one for Fiscal Year.  These slicers control two visuals based upon two different tables Invoice Lines and Sales Order Lines.

Right now I have turned off filtering of the slicer to the Sales Order Lines visual because I want to see all Open Orders regardless of the date.  This is working as expected, however the manager does not want to see any future dates included.

For examaple, I am looking at Invoices for March 2018 and the Sales Orders visual shows Open Orders Created Date from beginning in December 2017 through April 2018.  I need it to exclude dates greater than March 2018.  

I have tried the Dax formula below to use as a value filter on the visual, but all results show 1 no matter the date.

SOSelection = IF(SelectedValue('Sales Order Lines'[Created date]) > Max(CalendarTbl[Date]),0,1)

I have also tried the dax formula below.  When "Edit Interactions" are set to filter, it returns the last date of 3-31-18, (which is correct), but then only items created in March 2018 show as Open Orders. When the filter is turned off it returns 12-31-18, which shows all dates including April 2018.

LDate = LastDate(CalendarTbl[Date])

This is my first project in Power BI and any help would be greatly appreciated.  Thank you.

[shebr]

Hi @nandvroe,

I have the perfect link for you.. see below and thank me later.

https://youtu.be/-d_v3B8gmvI

[nandvroe]

shebr,

Thank you.  This is some great information, but unfortunately I don't think it will resolve my current issue.  I've actually tried this method, but it still didn't give me the results I needed.  I am so new to PBI however, it's very possible I did something wrong.

[shebr]

Hi @nandvroe,

Do you want to share some details of your data set, relationships and DAX measures you've used? I can try look at this for you, just tell me (visually) what results you are getting and what you are expecting.

Thanks

[nandvroe · Accepted solution]

shebr

I can't believe this!  I went back to get the data you requested and suddenly the measure I'd been working with started working.  I don't know why it suddenly started giving me the expected results (I've been working with it since last Thursday).  The only thing I can think of is that I deleted and recreated one of the relationships.

Anyway, I'm listing the solution below and marking this as solved.

SOMeasure1 = If(Max('Sales Order Lines'[Created date])>LastDate(CalendarTbl[Date]),0,1)

I added the above measure to my table and applied filter is "1".  Set "Edit Interactions" to filter all the page vizualiztions.

Thanks to all for the help!  Much appreciated!

[Anonymous]

Hi @nandvroe,

Could you please attach some screenshots highlighting your problem. Visualizing helps 🙂

[nandvroe]

ssobti,

Thank you for your response.  Below is a screen shot using sample data.  Notice the date filter is controling the chart and cards, but not the two tables below.  I have selected the month of March in the date filter. My goal in the two tables below is to show everything prior to March.  In other words the items with April dates should not show.  Right now I have "edit interactions" set to not filter the two tables.  

Hope my screen shot helps explain what I hope to accomplish.

Thank you for your time.