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 tables and measures from the original data model:

 

 

and the following tables:

- Calendar 

- Cut-off date - For example, Aug 30 to Sep 28 are considered marked as 'September' while July 30 to Aug 29 marked as "August"

- fTMS - transactional table

- RealPlate list - distinct of the plate numbers from fTMS table

- FleetNeg - lists down target trips of plate numbers each month. (Not all plate numbers from the fTMS are assigned a target trip each month)

 

Measure created:

 

Target Trips = 
SUMX (
    VALUES ( CutOff[Date] ),
    SUMX (
        FILTER (
            FleetNeg,
            CutOff[Date] >= FleetNeg[From]
                && CutOff[Date] <= FleetNeg[To]
        ),
        FleetNeg[Target_Trips_Abs] / ( FleetNeg[To] - FleetNeg[From] + 1 )
    )
)

 

 

Below is my problem with an example. If I create the following visuals and filter it from Aug 30 to Sep 28 (which is the September 2020 cut-off) and filter only AUA8552, I will get this result:

 

 

Take note that in the FleetNeg table, AUA8552 only has target trips for August and none in September. 

 

I understand why PBI is showing this results but what I need is that if the user sets the calendar slicer filtered for September cut-off (Aug 30 to Sep 28), then the lookup of Target Trips should be from September fleetNeg only. Thus, the result I wanted to see for AUA8552 should be 0 or none, since this plate number doesn't have a target trip in September FleetNeg.

 

How can I modify the measure to accomplish this?

 

Here is the sample pbix as well

 

https://drive.google.com/file/d/1UX0--4-V9b2Lu6g1xyMsawDEBXKGQJMQ/view?usp=sharing

 

 

Thank you!

Need Help

4 Replies

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