Calculated column to auto increment x number of rows based on condition

[imranami]

Hi everyone,

I am trying to add a calculated column to a Date table which will auto increment numbers (starting from 5 and ending with -5) based on the value of another boolean column. I have managed to achieve this in Excel via the screenshot below but struggling to recreate this with DAX. Any help would be appreciated.

https://1drv.ms/u/s!AkH_PeScw-iuhoIkgA2buE1BFiB7HQ?e=cszLPX

ImranAmi

[Icey]

Hi @imranami ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Greg_Deckler]

Take a look at Cthulhu, it does something similar:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739?search-action-id=95209223...

[parry2k · Accepted solution]

@imranami add this following column in your date table and I think it will do it

Flag = 
VAR __dateRange = CALENDAR ( Dates[Date] -5, Dates[Date] + 5 )
VAR __whereisWorkDay = CALCULATETABLE( VALUES(  Dates[IsLastWorkDay] ),ALL(), Dates[Date] IN   __dateRange )
VAR __workDayDate = CALCULATE( LASTDATE( Dates[Date] ), ALL(), Dates[Date] IN   __dateRange, Dates[IsLastWorkDay]= true() )
RETURN  IF ( true() IN __whereisWorkDay, DATEDIFF ( __workDayDate, Dates[Date], DAY ) )

[imranami]

@parry2k thank you so much this is exactly what I am trying to achieve (this was driving me insane)

@Greg_Deckler  thanks for the example and for the quick response

Awesome community

[parry2k]

@imranami  here is example of one period output