Filtering a Table by MIN and MAX Date selected on slicer

[mariena0615]

Good Afternon:

I need help with a project. I need to create an Aging based on dates selected on a Slicer. I have 3 Tables: 
1. Dates
2. vwChargesPower_BI by Service Date

3. Cube_Payments by Payment Date

We intend to create a Dymanic Report that if the person selects the date range from 07/01/2021 thru 06/30/2022 in a slicer (The Slicer has the Date from the table Date) It would recreate all the sales for that period and will filter the table Payments (to show payments with the same date on the slicer (07/01/2021 thru 06/30/2022 for the dates of services within that same range)

I set all the date formats to Date but for some reason is not working. The variable I create to show the min and max date selected on the slicer is returning a Datetime so the filter does not work.
Here is a sample of the code for the virtual table:

This is a sample of the measure for the selected date:

The relations on the tables are the following:
Dates -> PaymentsConditionalTableNew 
Field: Date -> Field Ddate = (Payment Date)
Dates -> vwChargesPower_BI
Field: Date -> Field: DServ = (ServiceDate)
vwChargesPower_BI -> Cube_Payments

Field: KeyUniqueYear -> Field:  KeyUniqueYear= (Unique ID)

Please advise. I am new working with Power BI.

Thankssss

[MarkLaf]

Based on your description, if you have relationships Dates -> ConditionalPayments, Dates -> Charges, and Charges -> ConditionalPayments, then one of those three relationships I believe is actually inactive and not doing anything (unless explicitly called in a measure with the USERELATIONSHIP function). You can check this by looking in your diagram view; if a relationship line is dotted instead of solid, it is inactive.

It looks like you are trying to produce a table visual that shows columns from Payments and Charges, filtered by the date in Payments. Is that correct? If you describe your overall objective re: a particular visual, we may be able to more directly address your problem.

I suspect you may want to update your model to something like the below:

If your active relationship is on Charge Date, for example, you could create and use the following measure to change a visual to use the inactive relationship:

Payment Date Active Relationship = 
CALCULATE( 
  SELECTEDVALUE( MergedChargesPayments[Payment Date] ), 
  USERELATIONSHIP( Dates[Date], MergedChargesPayments[Payment Date] ) 
)

(you would then probably rename the measure in visual to simply Payment Date)

[mariena0615]

Hi Marklaf:

Thanks so much for your help. We were able to do a workaround and produce the report changing the relationship. Still breaking my head on why my visual table does not work using the variable I created. Even after changing the relationships. 
Sample of Virtual table with variables:

Sample of Virtual table with hardcoded dates:

[MarkLaf · Accepted solution]

This is a calculated table, right? If so, you can't transfer the filter from any slicer in a report to the underlying data table (what I tried to explain in first response). Make a calculated table with just TestTable = ALLSELECTED(Dates[Date]) to see that the slicer has no impact on the output.

If you want some DAX you write to be responsive to a slicer, it must be a measure. A calculated table or calculated column simply do not interact with the report/slicers.

[mariena0615]

Thanks so much for taking the time to explain me this.

Regards;

Mariena

[MarkLaf]

It sounds like you have everything you need to get your dynamic table working with just a visual.

1) add a table visual to your canvas

2) drag and drop in the desired columns from CUBE_Payments (nAmountPlan1, nAmountPlan2, etc.)

3) add a slicer and drop in relevant field(s) from Dates

4) the slicer should just work as long as your relationships are set up correctly (e.g. Dates 1:M CUBE_Payments)

Also, one critical point to clarify, the model (source tables/columns and any calculated tables/columns) does not "talk" to slicers or any other filter context set in the report layer. The underlying model does not actually change / cannot be dynamic from the report/user view - it's only loaded once when someone opens the report in the service. The visuals, dynamic filters, and measures (which are fundamentally aggregations) on top of the model are what drive the dynamism of Power BI.

Also, FYI, for DAX datetime at midnight is the same as a date without time shown, .e.g. 10/4/2022 = 10/4/2022 12:00:00 AM.

[mariena0615]

Hi Marklaf:

Thanks so much for answering. 

Let me add some details to clarify. 

I am trying to filter the table CubePayments based on the dates selected by the user on the slicer. If I can create this filtered virtual table then I can create a mesure to calculate the balance as of the date specified by the user. For some reason I am unable to filter by dates. 

The relations are the following:

The relations on the tables are the following:
Dates -> PaymentsConditionalTableNew 
Field: Date -> Field Ddate = (Payment Date)
Dates -> vwChargesPower_BI
Field: Date -> Field: DServ = (ServiceDate)
vwChargesPower_BI -> Cube_Payments

Field: KeyUniqueYear -> Field:  KeyUniqueYear= (Unique ID)
vwChargesPower_BI- PaymentsConditionalTableNew 
Field: KeyUniqueYear -> Field:  KeyUniqueYear= (Unique ID)