Similar issue to the post regarding having 2 Relative Dates on one visual

[kkirner]

I've gotta thank @Anonymous for the response to that post.  That info got me closer than I had been after working on the issue for a while.

I'm trying to project closed files for my company, for the "current month" whenever the current month is.  Typically, a relative date filter would work well.  However, in this case we base look at upcoming closings based on one date and closings that occurred are based on another date.  Here's the measure I created (I know it's busy). 

Projected Closings =
var _MinDateClosingsOccurred = STARTOFMONTH(SPSRevenue[DatePaid])
var _MaxDateClosingsOccurred = TODAY()
var _MinDateClosingsRemaining = TODAY()
var _MaxDateClosingsRemaining = ENDOFMONTH(SPSOrder[Settlement Date])
var GrossClosingsRemaining = CALCULATE([distinctOrderCount], SPSOrder[Escrow Status]="In Process", SPSOrder[SettlementDateEstimated]="No")
var NetClosingsRemaining = CALCULATE(GrossClosingsRemaining, FILTER(SPSOrder, SPSOrder[Settlement Date] >= _MinDateClosingsRemaining && SPSOrder[Settlement Date] <= _MaxDateClosingsRemaining))
var GrossClosingsOccurred = CALCULATE([Closed File Count],LEFT(SPSRevenue[DisbursedToLedger],2)="R2")
var NetClosingsOccurred = CALCULATE(GrossClosingsOccurred, FILTER(SPSRevenue, SPSRevenue[DatePaid] >=_MinDateClosingsOccurred && SPSRevenue[DatePaid] <= _MaxDateClosingsOccurred))
var TotalProjClosings = NetClosingsRemaining+NetClosingsOccurred
return TotalProjClosings

I don't get any error, however, my result for the current month is 3,200+ and it should be more like 750-ish (93 remaining and 669 occurred)

[kkirner]

I think breaking it up into three measures has some merit and I like the commenting what I'm doing in the variable so that it is easy to see what's going on.

@ValtteriN thank you for thos suggestions.  I think I found out why I'm getting more results than anticipated with the "remaining" measure.

I told it to just return the "_MaxDateClosingsRemaining" so that I could make sure it was looking at June 30th.  When I did that, it errored out, as shown below.

This does make sense, since we'd have multiple files with the same settlement date.  Any ideas how to resolve this issue?

[ValtteriN]

Hi,

For the sake of organising I would split this into three measures:

[NetClosingsRemaining], [NetClosingsOccurred] and [TotalProjClosings]

This has the benefit of being easier to troubleshoot and it this way you can re-use your measures in different contexts. 

For your issue, as you stated I can't see any problems with the dax and since I don't have test data I don't know where the issue is. However here is your syntax as I understood it. Does this match your expected logic:

    NetClosingsRemaining =

    var _MinDateClosingsRemaining = TODAY() //this day

    var _MaxDateClosingsRemaining = ENDOFMONTH(SPSOrder[Settlement Date]) // end of month

    var GrossClosingsRemaining = CALCULATE([distinctOrderCount], SPSOrder[Escrow Status]="In Process", SPSOrder[SettlementDateEstimated]="No") //calculate distinct count when SPSOrder[Escrow Status]="In Process" and SPSOrder[SettlementDateEstimated]="No"

    return

    CALCULATE(GrossClosingsRemaining, FILTER(SPSOrder, SPSOrder[Settlement Date] >= _MinDateClosingsRemaining

    && SPSOrder[Settlement Date] <= _MaxDateClosingsRemaining)) //calculate distinct count when SPSOrder[Escrow Status]="In Process" and SPSOrder[SettlementDateEstimated]="No" and SPSOrder[Settlement Date] between today and endofmonth

NetClosingsOccurred =

var _MinDateClosingsRemaining = TODAY()

var _MinDateClosingsOccurred = STARTOFMONTH(SPSRevenue[DatePaid])

var GrossClosingsOccurred = CALCULATE([Closed File Count],LEFT(SPSRevenue[DisbursedToLedger],2)="R2") //calculate closed file count where two leftmost characters of SPSRevenue[DisbursedToLedger] are R2

return

CALCULATE(GrossClosingsOccurred, FILTER(SPSRevenue, SPSRevenue[DatePaid]

>=_MinDateClosingsOccurred && SPSRevenue[DatePaid] <= _MinDateClosingsRemaining)) //calculate closed file count where two leftmost characters of SPSRevenue[DisbursedToLedger] are R2 and SPSRevenue[DatePaid] between start of month and today

If the date ranges as described above are correct you should check the logic of 

[Closed File Count] and  [distinctOrderCount]

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[kkirner]

@ValtteriN I can't accept it as solved yet.  Here's what I wrote in response to your suggestion.

I think breaking it up into three measures has some merit and I like the commenting what I'm doing in the variable so that it is easy to see what's going on.

@ValtteriN thank you for thos suggestions.  I think I found out why I'm getting more results than anticipated with the "remaining" measure.

I told it to just return the "_MaxDateClosingsRemaining" so that I could make sure it was looking at June 30th.  When I did that, it errored out, as shown below.

This does make sense, since we'd have multiple files with the same settlement date.  Any ideas how to resolve this issue?