Stuck with Date Problem

[mgrayTCB]

The measure below works if I use hard cod dates (shown in red below) but not if I calculate the dates. The date calculations return the dates I want but I get the values for dates in between. I only want the values on the _dmax date and the _dmin date that I calculate using the variables.

Sum Net Rent (1 unit Point2Point) =

var _dmax = EOMONTH(min(dimCalendar[Date]),0)

var _MonthsBack = if(ISBLANK(min(stblIntergers[Value])),-1,min(stblIntergers[Value])*-1)

var _dmin = EOMONTH(date(year(_dmax),MONTH(_dmax)+_MonthsBack,day(_dmax)),0)

RETURN

if(HASONEVALUE(factsAffordableUnit[UnitCodeLU]),

 CALCULATE(

    [Sum Net Rent],

 filter(dimCalendar,dimCalendar[Date] = _dmin || dimCalendar[Date] = _dmax))

  // THIS Works

 //filter(dimCalendar,dimCalendar[Date] = date(2025,2,28) || dimCalendar[Date] = date(2025,6,30))

 )

using the calculated dates I get

Using the hard coded dates I get what I want. Values only on those two dates.

Any help would be appreciated before I pull my hair out.

[v-pnaroju-msft]

Hi mgrayTCB,

Kindly provide some sample data which explains your problem or question clearly in a simple and neat format, not as an image. This will help us to understand and solve the issue more effectively. Please ensure the data is relevant, does not have any sensitive details, and is connected to your problem. Also, let us know what result you expect from this sample.

Thank you.

[v-pnaroju-msft]

Hi mgrayTCB,

Please share some sample data that shows your problem or question clearly in a simple and organized way (not as an image). This will help us understand and solve the issue better. Make sure the data is relevant, does not have any sensitive information, and is related to your problem. Also, please tell us what result you expect from this example.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin, for your response.

Hi mgrayTCB,

We appreciate your question on the Microsoft Fabric Community Forum.
We kindly request you to provide sample data that clearly demonstrates your issue or query in a structured format (not as an image) to help us understand and resolve the matter. Please ensure that the data is relevant, free from any sensitive information, and directly related to the issue. Additionally, please share the expected outcome based on the given example.

Thank you.

[mgrayTCB]

I think I isolated the problem but I am still stuck. Below is the desired result. The megenta line connects the max date with the date 5 minutes earlier. It works fine if I hard code the max date. 

The problem happens when I try to harvest the user selected max date and use that to count backwards to the min date. I end up with all the dates in 2025.

As you can see my harvested ddate is picking up the correct date. (retured _dmax below to varify).

Sorry about the screen shots. Cant think of a way to simplify and sanitize the model for sharing.

[mgrayTCB]

The IN syntax doesnt change anything. I have tried it.

[lbendlin]

What is the expected outcome?  The [Sum Net Rent] for these two days, or for the date range between them?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[mgrayTCB]

Yes the expected outcome is just  [Sum Net Rent] on the two dates no data on anyother dates.

[mgrayTCB]

Each variable returns the correct date. I have also tried IN instead of the OR syntax but no change. Other ideas?

[lbendlin]

Use EVALUATEANDLOG to check your variable values. Most likely one of them or both returns BLANK.

Also note that you can use IN

if(HASONEVALUE(factsAffordableUnit[UnitCodeLU]),

 CALCULATE(

    [Sum Net Rent],

 filter(dimCalendar,[Date] IN {_dmin,_dmax}))

 )