Naming of specific period for filtering

[pippo80]

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

[v-caliao-msft · Accepted solution]

@pippo80,

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

[GilbertQ]

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...