Forum Discussion
Post PatronComplicated DAX using SUMX
Actually, you should ignore all my posts. The formula written by the developer is good. There is no error in it.
Earlier, I overlooked one point. It is in the following line of code.
CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )The explanation is, there is a relationship between "Calendar[Date]" field to "ftms[date_backinplant]" field. So when you plot the measure to a matrix with "Calendar[Date]" on columns and "fmts[RealPlate]" on the rows, these two fields filter the fTMS table and as a result, only those records with "date_backinplant" and "realplate" has records on any given date is passed on to the calculation. But you need the "24 Hours" for all the dates betwen "date_inline" and "date_backinplant". To do this, the filter from "Calendar[Date]" field on "ftms[date_backinplant]" had to be removed. It was done using CROSSFILTER() function with "NONE" as the 3rd paramenter which removes the filter from "Calendar[Date]" on fTMS table.
All is perfect till now. But the problem arised when you had the following record in your data.
DTSStage time_inline time_backinplant date_inline date_backinplant RealPlate COMPLETED 02:00:00 21:24:00 06-03-2020 08-06-2020 ABC335 In this record, the date_backinplant has a value of 6th Jun, but date_inline has a value of 6th Mar, and because of the CROSSFILTER with "NONE" as the parameter, this record was not filtered out although date_backinplant's 6th Jun date falls outside your date selection. Therefore, from 6th Mar 2020 onwards, for every day, the formula inserted 24 Hours till the last day in your matrix which is 29th March 2020.
If you want to omit the records with "date_backinplant" falling outside the range of your selected date range, you will have to exclusively add a filter to your fTMS table in the code somewhere near the filter on "COMPLETED" stage.
I would like to recreate your scenario on my PBI Desktop. Will you be able to send some sample data. I have tried using the sample data you have posted. It seems it is inadequate.
I have the following sample data tables from your post.
1) Calendar
2) ftcFleetNeg
3) fTMS
4) RealPlateList
If you think these data is enough to recreate the scenario, please send some relevant entries for the following table also
"Table Cutoff - is like a Calendar table but has a column that classifies which dates would fall into their "cut off period". For example, they labeled "March" dates from Feb 28 to March 29."
The CutOff table is also used in the DAX code. So I will need some relevant data for this table also.
Further, You said there is a date filter on the report. Is it filtered based on "Calendar[Date]" or fTMS[date_backinplant] ?
It will be helpfult if you can create a pbix file with all sample data and DAX codes so that we can easily fix it.
Post PatronHi, Anonymous ! I copied only the relevant columns from the database and uploaded in pbix file. I also already put in the measures.
Another weird thing I find is that even if I try to replicate this part of the report like this one, I still don't get the correct numbers even though I just copy the measures and the data. But anyway, the output in the sample pbix has the same "sickness" wherein there's 24 hours being shown on days that the truck had no trips.
https://drive.google.com/file/d/1xpJyjpu54H0Penra0PJ124MsguGtdXMl/view?usp=sharing