topic Re: excluding the Date Flagged in DAX Commands and Tips

excluding the Date Flagged

Kris48 — Sat, 09 Nov 2024 15:32:10 GMT

I would like to add logic to exclude days that have the "T" flag on the calendar in measure: "Value_measure_1"

URL pbix: https://drive.google.com/file/d/1rTsECeGnNXDVG0TR-_tkpnNRZnQGRv9K/view?usp=sharing

Re: excluding the Date Flagged

Selva-Salimi — Sat, 09 Nov 2024 17:31:31 GMT

Hi Kris48

you should update your measure as follows:

Value_measure_1 =

var _Y= DATESBETWEEN('Calendar'[Date], SELECTEDVALUE('Fact'[Data from]), SELECTEDVALUE( 'Fact'[Data to]))

var _Tdates = summarize(FILTER('Calendar','Calendar'[Flag] in {"T"}),'Calendar'[Date])

var _X= FILTER(_Y , 'Calendar'[Date] in _Tdates)

VAR CurrentDate = TODAY()

VAR FilteredFact =

FILTER(

'Fact',

'Fact'[Data from] > CurrentDate

)

VAR LastRevValue =

CALCULATE(

LASTNONBLANKVALUE(Rev[Date] , SUM('Rev'[Value])), TREATAS(VALUES('Fact'[INDEX]) , Rev[INDEX])

)

VAR DaysBetween =

SUMX(

FilteredFact,

DATEDIFF('Fact'[Data from], 'Fact'[Data to], DAY)+1

) - COUNTROWS(_X)

RETURN

LastRevValue * DaysBetween

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

Re: excluding the Date Flagged

Kris48 — Sat, 09 Nov 2024 17:13:51 GMT

Selva-Salimi

Thank you, but unfortunately it doesn't work well. I took your variable into a separate measure and named the measure "_Tdates_X" .
Unfortunately, it wrongly counts the days that are supposed to have flag = "T"
PBIX: https://drive.google.com/file/d/1Zf_h6B2iYrYrfrnO9qJEmReXMk9cYPYb/view?usp=sharing

Re: excluding the Date Flagged

Selva-Salimi — Sun, 10 Nov 2024 05:10:41 GMT

@Kris48

yes you are right. update _Y variable as follows:

var _Y= DATESBETWEEN('Calendar'[Date], SELECTEDVALUE('Fact'[Data from]) , SELECTEDVALUE( 'Fact'[Data to]))

* I've updated my previous response to correct a part of the measure based on feedback. this will make it easier for others to find the right answer quickly. thanks for feedback