Measure to count differences between dates

[SamCoates]

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:

ReleasedToDate = CALCULATE(sum('Sharepoint Tracker'[ReleasedFlag]),all('Sharepoint Tracker'),'Sharepoint Tracker'[Release Date]<=EARLIER('Sharepoint Tracker'[Planned Date].[Date]))

How should i go about being able to visulise this with filtered data please?

Thanks,

Sam

[Anonymous · Accepted solution]

// 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
)

[SamCoates]

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 __count

Thanks again!

[Anonymous]

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.

[SamCoates]

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

[Anonymous]

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

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[SamCoates]

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

[SamCoates]

Bump

Does anyone have any advice on how I should go about this please?

Thanks,

Sam

[wdx223_Daniel]

ReleasedToDate = CALCULATE(sum('Sharepoint Tracker'[ReleasedFlag]),FILTER(all('Sharepoint Tracker'),'Sharepoint Tracker'[Release Date]<=MAX('Sharepoint Tracker'[Planned Date].[Date])))

think this code might work, but it is not the best.