Forum Discussion
Date Range Dax
trying to figure out how to generate date range in Dax based on dropdown week number column in my PBI report. I created powerapp with a dropdown called (Dropdown1_2) and in it are numbers 1-52 that represents the calendar week in a year. For example, if you were to select Week 31 from the dropdown, the calculation for the label, i created, right next to the dropdown would say "08/01/2022 - 08/06/2022". This label value is not saved in a table though....
so i bring in the table to power bi. Again the label range is not captured but i would like to possibly create a measure in the Power BI that when the week field (Dropdown1_2) is filtered on week 31, the measure would show "08/01/2022 - 08/06/2022" or whatever week per the value. I believe i could do this in DAX, but i dont know how to write the code. The code i used for the label in powerapps is:
Text(DateAdd(DateAdd(DateValue("1/1/2022","en-US"),(7*Dropdown1_2.Selected.Value),Days),-5,Days),"mm/dd/yyyy")&" - "&Text(DateAdd(DateValue("1/1/2022","en-US"),(7*Dropdown1_2.Selected.Value),Days),"mm/dd/yyyy")
For the Measure that i will add to the report, what code would i need to setup in DAX to mimick the date range???
Hi Anonymous ,
According to your description, if you want the date range for week 31, it should be “07/31/2022 – 08/06/2022”, here is my solution.
Put “WeekNumber” into slicer, and select “Dropdown”.
Create a measure.
Date Range = MAX ( DATE ( 2022, 1, 2 ), DATE ( 2022, 1, 1 ) - WEEKDAY ( DATE ( 2022, 1, 2 ) ) + ( MAX ( 'Dropdown1_2'[WeekNumber] ) - 1 ) * 7 + 2 ) & " - " & MIN ( DATE ( 2023, 1, 1 ), DATE ( 2022, 1, 2 ) - WEEKDAY ( DATE ( 2022, 1, 2 ) ) + MAX ( 'Dropdown1_2'[WeekNumber] ) * 7 )Final output:
Best Regards,
Community Support Team _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
johnt75Super User
Create a proper date table which includes a week number column then use that column on your slicer. You would also need to set either a filter or slicer to set the year, or allow your users to choose the year. You could then create a measure to show the selected date range like
Selected date range = FORMAT ( MIN ( 'Date'[Date] ), "mm/dd/yyyy" ) & " - " & FORMAT ( MAX ( 'Date'[Date] ), "mm/dd/yyyy" )- v-xiaosun-msft
Community Support
Hi Anonymous ,
According to your description, if you want the date range for week 31, it should be “07/31/2022 – 08/06/2022”, here is my solution.
Put “WeekNumber” into slicer, and select “Dropdown”.
Create a measure.
Date Range = MAX ( DATE ( 2022, 1, 2 ), DATE ( 2022, 1, 1 ) - WEEKDAY ( DATE ( 2022, 1, 2 ) ) + ( MAX ( 'Dropdown1_2'[WeekNumber] ) - 1 ) * 7 + 2 ) & " - " & MIN ( DATE ( 2023, 1, 1 ), DATE ( 2022, 1, 2 ) - WEEKDAY ( DATE ( 2022, 1, 2 ) ) + MAX ( 'Dropdown1_2'[WeekNumber] ) * 7 )Final output:
Best Regards,
Community Support Team _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.