Forum Discussion
Measure to count differences between dates
Hello,
I have created a column that calculates how many rows have a 'Release Date' < the current row's 'Planned Date'. I am then plotting this on a line chart. This works well until a filter gets applied as the numbers in my column are based on unfiltered data.
I presume the best way to get round this is with a Measure, however this fails with the 'Earlier' function.
The column formula is:
// Assuming that there is // a column in the table // which uniquely identifies // the rows, ItemID. T // is your table. // Due to the use of ALLSELECTED // this measure must never be // used in interators. It's a top-level // measure. [ReleasedToDate] = if( hasonevalue( T[ItemID] ), var __plannedDate = selectedvalue( T[Planned Date] ) var __count = calculate( countrows( T ), T[Release Date] < __plannedDate, allselected( T ) ) return __count )
9 Replies
SamCoates , if create date is less than planned date why need earlier
This type of column should do
if( [Release Date] <[planned date] ,1,0)
Measure
if( max([Release Date]) <Max([planned date]) ,1,0)
But in case of measure you need row context, Like ID or sonething
Sumx(Values(Table[ID]),if( max([Release Date]) <Max([planned date]) ,1,0) )
Refer for row context
Hi, thanks a lot for your response, but unfortunately that doesn't work as I'd hoped.
I am after a measure that tells me how many lines there are in the entire table that have a 'Released Date' < the current line's Planned Date.
The counter above will just tell me how many lines have a Release Date that is earlier thatn the Planned Date.
Is that possible with a measure?
Thanks,
Sam
Bump
Does anyone have any advice on how I should go about this please?
Thanks,
Sam
ReleasedToDate = sumx( filter( 'Sharepoint Tracker', 'Sharepoint Tracker'[Release Date] <= 'Sharepoint Tracker'[Planned Date] ), 'Sharepoint Tracker'[ReleasedFlag] )Please learn more about how DAX works. EARLIER is a function to use only within iterators that are executed within other iterators and these days there's no need to use it at all since there is a better mechanism: variables. This function is deprecated (see dax.guide).
Also, please stop using the auto-generated date hierarchies. They are only for complete newbies and make a lot of trouble in the end (not to mention they are taking space and you can't make one hierarchy filter other date columns in other tables). Always create your own calendar(s). ALWAYS.
Thank you wdx223_Daniel, unfortunately your formula returns the total number of lines released before the largest date in the entire table, so it gives me a flat line for the current release total rather than showing what the total was on each day.
And thank you to Anonymous who on his way to misreading the question and delivering a solution that doesn't relate to the question - proved that manners cost nothing and being condescending makes you look a bit silly when you are unable to provide the solution yourself.
Such a shame as most other users on here have been very helpful and polite, and don't see the need to belittle people's ability when they are simply asking more experienced users for assistance. (p.s. I am a 'Complete Newbie' and therefore don't see that as a derogatory term. Sorry to hear that you do.)
- SamCoates
You have read too much into my reply. There's nothing derogatory in my reply and all I typed in there is true. Even in Microsoft documentation they write that such hierarchies should only be used in the simplest of models and models that are 'quick and dirty,' never in production-ready ones. Sadly, most people that are new to PBI (newbies - I guess this is the right word?) do not know what a good model should look like and overuse the automatic hierarchies, which immediately leads to many problems.
Therefore the usage of auto-generated hierarchies, unfortunately, often IS a sign that you most likely are a newbie. I can't see anything derogatory about this statement. It's just a fact of life.
Sorry you have misread my intention.
// Assuming that there is // a column in the table // which uniquely identifies // the rows, ItemID. T // is your table. // Due to the use of ALLSELECTED // this measure must never be // used in interators. It's a top-level // measure. [ReleasedToDate] = if( hasonevalue( T[ItemID] ), var __plannedDate = selectedvalue( T[Planned Date] ) var __count = calculate( countrows( T ), T[Release Date] < __plannedDate, allselected( T ) ) return __count )Thank you very much Anonymous that set me on the right path. I had to filter out any blank cells, and for some reason it was returning very few results when i used the unique ID field, not sure why. However a few mods led me to this which works a treat.
ReleaseCounter =var __plannedDate =selectedvalue( 'Sharepoint Tracker'[Planned Date] )var __count =calculate(countrows( 'Sharepoint Tracker' ),'Sharepoint Tracker'[Release Date] <= __plannedDate,allselected( 'Sharepoint Tracker'),not(isblank('Sharepoint Tracker'[Release Date])))return __countThanks again!
