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 there,
I would ike to creat a column naming specific date ranges in order to create a filter afterwards. Right now I have set up the follwing IF-statement but it is rather a long formula, specifically if I want to add more weekly periods afterwards:
Date Range = IF('[DimDate] = DATE(2017;06;22);"22.-28.06";IF(''[DimDate] = DATE(2017;06;23);"22.-28.06";IF('[DimDate] = DATE(2017;06;24);"22.-28.06";IF('[DimDate] = DATE(2017;06;25);"22.-28.06";IF('[DimDate] = DATE(2017;06;26);"22.-28.06";IF('[DimDate] = DATE(2017;06;27);"22.-28.06";IF('[DimDate] = DATE(2017;06;28);"22.-28.06";"")))))))
Is there a way of doing it in a 'smarter' way? I am quite new to DAX...
Thanks
P
Solved! Go to Solution.
Maybe you can use the DAX below instead.
Date Range = IF(WEEKDAY('Table'[Date])<5,DAY('Table'[Date]-WEEKDAY('Table'[Date])-2)&"-"&DAY('Table'[Date]-WEEKDAY('Table'[Date])+4)&" "&RIGHT("00"&MONTH('Table'[Date]-WEEKDAY('Table'[Date])+4),2),DAY('Table'[Date]-WEEKDAY('Table'[Date])+5)&"-"&DAY('Table'[Date]-WEEKDAY('Table'[Date])+11)&" "&RIGHT("00"&MONTH('Table'[Date]-WEEKDAY('Table'[Date])+11),2))
Regards,
Charlie Liao
Maybe you can use the DAX below instead.
Date Range = IF(WEEKDAY('Table'[Date])<5,DAY('Table'[Date]-WEEKDAY('Table'[Date])-2)&"-"&DAY('Table'[Date]-WEEKDAY('Table'[Date])+4)&" "&RIGHT("00"&MONTH('Table'[Date]-WEEKDAY('Table'[Date])+4),2),DAY('Table'[Date]-WEEKDAY('Table'[Date])+5)&"-"&DAY('Table'[Date]-WEEKDAY('Table'[Date])+11)&" "&RIGHT("00"&MONTH('Table'[Date]-WEEKDAY('Table'[Date])+11),2))
Regards,
Charlie Liao
Hi @pippo80
What I would suggest is to create a Date Table, and in your Date table you can then add new columns in the Query Editor. In the Query Editor there is an option for a Conditional Column where you can easily specify your conditions for the column.
This also then means that the data is loaded into your Data Model and ready to use.
You can use this as an example to create the Date Table: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |