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.
Hello All,
I'm not sure if creating a calendar is needed for this but I'm trying find a way to convert a date to a weekly range for use in reporting. For example, when the date falls within a respective SundayDateYear - SaturdayDateYear range, then the weekly range would be that SundayDateYear - SaturdayDateYear. Then I would use that SundayDateYear - SaturdayDateYear range as a filter mechanism on my desktop visual.
The below formula works in Excel but it is not working the same in Power BI.
TEXT('xyz'-COLUMN+7-MOD('xyz'-1,7)+(COLUMNS('xyz')-1)*7, "mm/dd/yy")&" - "&TEXT('xyz'+7-MOD('xyz'-1,7)+(COLUMNS('xyz')-1)*7+6, "mm/dd/yy")
I am thankful for any recommendations and tips.
Hi @shamwoo
Could you show me which columns in excel you used in this formula and what the result of this formula so that it is clear for me to analyze?
Best Regards
Maggie
Hi Maggie,
I sincerely appreciate you for reaching out! Please find an example of what I am looking at.
Column A - Column E contains dates on when the row item was handled. Column F contains the date when the row item was last handled. I am looking for a DAX that creates a period range (Saturday to Sunday) using Column F as a comparison column, but bases whichever date is closest in Column A - Column E as the beginning of the period range.
For example, on the second to last row the Last Modified Date was 08/20/18 and searching through Column A - Column E, Column B has the nearest date. So a Period Range of 08/16/18 - 08/22/18 will be created.
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |