Applying Calculated Measure within another Measure

[Anonymous]

Hi,

I previously created two measures (with the help on this community!!). The purpose of the first measure was to find the days between the most recent entry in the table and a fixed date. The second measure was to use the days between to determine the date range between the fixed date and the most recent entry. Both measures worked well for my purpose. This is detailed below:

Days Between = calculate( countrows(date_table), DATESBETWEEN( date_table[Date_field].[Date], date(2020,11,01),

MAX(tblInvestigations[Date Received])))

 

Rolling Measure Before = CALCULATE(COUNTROWS(tblInvestigations),DATESINPERIOD(date_table[Date_field],date(2020,11,01),-[Days Between],DAY))

I wanted to integrate  [Rolling Measure Before] into another measurement with the aim of filtering for this new  date range. I tried the below and it gave me blanks with no error. Removing the [Rolling Measure Before] measure populated the card indicating the the [Date Recieved] = [Rolling Measure Before] part isn't working properly.

External (Before) =calculate(countrows(tblInvestigations),

FILTER( tblInvestigations, [Date Received] = [Rolling Measure Before] && [Col_1 ] <> BLANK() &&

[Col_1] <> "N/A"))

I would appreciate any advice or directions!

Cheers

[Anonymous]

Hi Amit, Sorry for the late reply was on break. 

Your post managed to spark me to try something new which was to take out the measure from the filter and apply it next to the count rows like this:

CALCULATE(COUNTROWS

(tblInvestigations),DATESINPERIOD(date_table[Date_field],date(2020,11,01),-[Days Between],DAY),

FILTER(tblInvestigations,[Column_1] = "X"))

Thanks!

[amitchandak]

@Anonymous ,You are comparing

[Date Received] = [Rolling Measure Before]

I doubt Rolling measure Before do not return date.

what you want to achieve here, you can for isblank( [Rolling Measure Before])

or

not isblank( [Rolling Measure Before])

[Anonymous]

Hi Amit,
Did you mean inside the filter like this or outside?

External (Before) =

calculate(countrows(tblInvestigations), FILTER( tblInvestigations, NOT(ISBLANK([Rolling Measure Before])) && [Col_1 ] <> BLANK() && [Col_1 ] <> "N/A")))

This returns blanks and removing the NOT part is the same as removing the [Rolling Measure Before] part.

[amitchandak]

@Anonymous , the measure seems correct.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.