Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Take a look at Cthulhu, it does something similar:
@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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
@imranami here is example of one period output
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |