Forum Discussion
Convert excel function to DAX
Anonymous
This calculated column will give you the date on which the absence ENDS (will return only non-blank on the rows where an absence starts). Do note that the code is practically the same as the previous one, it just retunrs something different (a different VAR). The code calculates quite a number of VARS so you can use them to return other things you might be interested in (such as the number of consecutive absence days)
Last date of absence = IF ( NOT AttendanceMaster[IsPresent]; VAR NextPresence_ = CALCULATE ( MIN ( AttendanceMaster[AttendanceDate] ); ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] ); AttendanceMaster[AttendanceDate] > EARLIER ( AttendanceMaster[AttendanceDate] ); AttendanceMaster[IsPresent] = TRUE () ) VAR lastAbsenceDay_ = CALCULATE ( MAX ( AttendanceMaster[AttendanceDate] ); ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] ); AttendanceMaster[AttendanceDate] < NextPresence_ ) VAR NumConsecutiveAbsentDays_ = CALCULATE ( COUNT ( AttendanceMaster[AttendanceDate] ); ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] ); AttendanceMaster[AttendanceDate] >= EARLIER ( AttendanceMaster[AttendanceDate] ); AttendanceMaster[AttendanceDate] < NextPresence_ ) VAR isStartOfAbsence = VAR previousDate_ = CALCULATE ( MAX ( AttendanceMaster[AttendanceDate] ); ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] ); AttendanceMaster[AttendanceDate] < EARLIER ( AttendanceMaster[AttendanceDate] ) ) RETURN IF ( ISBLANK ( previousDate_ ); TRUE (); CALCULATE ( DISTINCT ( AttendanceMaster[IsPresent] ); ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] ); AttendanceMaster[AttendanceDate] = previousDate_ ) ) RETURN IF ( isStartOfAbsence; lastAbsenceDay_ ) )Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi my friend,
This is the link of sampel data on excel file:
This is the link of Pbix file:
Thank you for your help
Hi Anonymous
Can you explain what the measure is supposed to do and where/how you are going to use it (in a visual, with what fields in the visual, etc.)?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
I have attached the excel file containing excel functions in columns H and I. I ask for your help to change that function to DAX function. I will add two new columns to Power BI.
- The conversion result of the excel function in Column H will be used as a start date when the child is not present in class
- The conversion result of the excel function in column I is end date when child absent and just an evaluation of how many days the child is absent.
I try to see consecutive days in a more systematic way. But the disadvantage of the excel function is working days in a year. Can you help me with this too by including holidays like Sundays in the calculation. After that I will make count of start absent days to get the total children who has absent 3 or more consecutive days in a year.
Thank you
Anonymous - See if this assists: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991
Anonymous
So you do not need a measure but calculated columns, it seems. In any case, please explain the ultimate result you want out of this, instead of talking about translating formulae from Excel. The approach in DAX will probably be different. Do you want to identify the children that have been abstent at least three days in a row? Do you want to count them? What do you need exactly???
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
