Forum Discussion

newgirl's avatar
newgirl
Post Patron
5 years ago

Cut Off Dates

Hi, guys!   I'm working on a  project that has a big data model and several measures but there is a certain part I need to mofify. Below is the sample data model that only shows the involved table...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi newgirl ,

Please update the formula of measure [Target Trips] as below and check whether it can get your expected result.

Target Trips = 
var _mindate=MIN('Cutoff'[Date])
var _maxdate=MAX('Cutoff'[Date])
var _selcutoffYear=CALCULATETABLE(VALUES('Cutoff'[Off Hire Year]),DATESBETWEEN('Cutoff'[Date],_mindate,_maxdate))
var _selcutoffhmonth=CALCULATETABLE(VALUES('Cutoff'[Off Hire Month]),DATESBETWEEN('Cutoff'[Date],_mindate,_maxdate))
return 
SUMX (
    VALUES ( CutOff[Date] ),
    SUMX (
        FILTER (
                   FleetNeg,
            CutOff[Date] >= FleetNeg[From]
                && CutOff[Date] <= FleetNeg[To]
                &&'FleetNeg'[Year] in _selcutoffYear
                &&'FleetNeg'[Month] in _selcutoffhmonth
) ,
        FleetNeg[Target_Trips_Abs] / ( FleetNeg[To] - FleetNeg[From] + 1 )
    )
)

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

newgirl's avatar
newgirl
Post Patron
5 years ago

Hi Anonymous !

I tried your formula in my sample pbix file! The lookup worked correctly but it filtered out Aug 30 and 31 even though the page is filtered from Aug 30 to Sep 28.

 

Since Aug 30 and 31 are considered in the September cut-off in the CutOff table, they still need to show in the page if it's filtered from Aug 30 to Sep 28. 

 

Any other modification that can be done in the measure?

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi newgirl ,

    Please update the formula as below and check whether it can get the expected result. Any comment or problem, please feel free to let me know.

    Target Trips = 
VAR _mindate= MIN('Cutoff'[Date])
VAR _maxdate= MAX('Cutoff'[Date])
VAR _selcutoffYear =
    CALCULATETABLE (
        VALUES ( 'Cutoff'[Off Hire Year] ),
        DATESBETWEEN ( 'Cutoff'[Date], _mindate, _maxdate )
    )
VAR _selcutoffhmonth =
    CALCULATETABLE (
        VALUES ( 'Cutoff'[Off Hire Month] ),
        DATESBETWEEN ( 'Cutoff'[Date], _mindate, _maxdate )
    )
RETURN
    IF (
        MAX ( 'FleetNeg'[Year] ) IN _selcutoffYear
            && MAX ( 'FleetNeg'[Month] ) IN _selcutoffhmonth,
        SUMX (
            VALUES ( CutOff[Date] ),
            SUMX (
                FILTER (
                    FleetNeg,
                    CutOff[Date] >= FleetNeg[From]
                        && CutOff[Date] <= FleetNeg[To]
                ),
                FleetNeg[Target_Trips_Abs] / ( FleetNeg[To] - FleetNeg[From] + 1 )
            )
        ),
        IF ( NOT ( ISBLANK ( MAX ( 'FleetNeg'[From] ) ) ), 0, BLANK () )
    )

    Best Regards
    Rena
    Community Support Team _ Rena Ruan
    If this post helps, then please consider Accept it as the solution to help the other members find it more.

    • newgirl's avatar
      newgirl
      Post Patron
      5 years ago

      Hi Anonymous !

       

      The formula you created now includes the Aug 30 to Aug 31 🙂

       

      However, when I look at the numbers under Aug 30 and 31 columns, the basis of the target trips of these dates are from August FleetNeg table.

       

       

       

       

      The desired output is that these columns would still derive the target trips from the September FleetNeg since the calendar dates of 'Aug 30 to Sep 28' are treated as September 2020 cut-off, based on the cut-off table.

       

       

      For example in the photo below, the expected output should have been 0.83 instead of 0.81. (red arrow means what is being shown while green arrow is what is needed to be shown)

       

      Can the measure still be tweaked to follow this condition? :-S