Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kkirner
Helper II
Helper II

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

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)

3 REPLIES 3
kkirner
Helper II
Helper II

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.

kkirner_0-1685644187569.png

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

ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

kkirner_0-1686066538172.png

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.