topic Re: Power BI Dax issue in calculating last date for selected date range in DAX Commands and Tips

Power BI Dax issue in calculating last date for selected date range

shaileshp — Thu, 06 Oct 2022 08:54:22 GMT

I am stuck in one of the scenario of power bi. I am using Table visual to show weekday name, first date, last date as per below.

User can select custom date range(for example I have selected 1st July 2022 to 31st July 2022) and this will be filters on all pages. Also there is one calculated column which will describe whether its weekday or weekend, used as hidden slicer with weekday as default value.

Its showing 29th July as LastDate in above visual considering 31st july as Sunday. I want to show 31st july as last day irrespective of selected slicer value.

Measures:

FirstDate = FIRSTDATE(ALLSELECTED('Calender Dates'[Date]))

LastDate = LASTDATE(ALLSELECTED('Calender Dates'[Date]))

Slicers: IsCalDayWeekDayOrWeekend(calculated column which will consider Mon to Fri as weekday. Sat and Sun as weekend). Default selected as weekday.

I have tried ALL, ALLEXCEPT but it did not give right results. Please suggest and Thanks In Advance.

Re: Power BI Dax issue in calculating last date for selected date range

daXtreme — Thu, 06 Oct 2022 09:11:09 GMT

Try this one:

LastDate = CALCULATE( MAX( 'Calender Dates'[Date] ), REMOVEFILTERS( 'Calender Dates'[IsCalDayWeekDayOrWeekend] ), ALLSELECTED( 'Calender Dates' ) )

Re: Power BI Dax issue in calculating last date for selected date range

ValtteriN — Thu, 06 Oct 2022 09:22:06 GMT

Hi,

This part should cause the behaviour you described: "IsCalDayWeekDayOrWeekend(calculated column which will consider Mon to Fri as weekday. Sat and Sun as weekend). Default selected as weekday."

ALLSELECTED will apply the weekday filter to the lastdate and remove the weekend from the range.

Try something like this:

MAX = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date]),ALL('Table'[isCalDayWeekDayOrWeekend]))

This way the filter context should be correct.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Power BI Dax issue in calculating last date for selected date range

shaileshp — Thu, 06 Oct 2022 10:40:46 GMT

Hi ValtteriN,

Thank you for your reply. I have tried this, unfortunately its not giving the expected output.

Re: Power BI Dax issue in calculating last date for selected date range

daXtreme — Thu, 06 Oct 2022 10:40:55 GMT

OK, this is what your date table should look like:

And here are the two measures you're after:

FirstDate v2 = var MinWeekID = CALCULATE( MIN( T[WeekID] ), ALLSELECTED( T ) ) var Output = CALCULATE( MIN( T[Date] ), T[WeekID] = MinWeekID, REMOVEFILTERS( T ) ) return Output

and

LastDate v2 = var MaxWeekID = CALCULATE( MAX( T[WeekID] ), ALLSELECTED( T ) ) var Output = CALCULATE( MAX( T[Date] ), T[WeekID] = MaxWeekID, REMOVEFILTERS( T ) ) return Output

Re: Power BI Dax issue in calculating last date for selected date range

shaileshp — Thu, 06 Oct 2022 10:45:03 GMT

Hi daXtreme,

Thanks for your reply, i have tried the suggestion, however now it takes weekdayname in context and gives the last date respective to week day name.

Thanks.

Re: Power BI Dax issue in calculating last date for selected date range

daXtreme — Thu, 06 Oct 2022 10:48:24 GMT

You should read all my posts... Solution attached.

Re: Power BI Dax issue in calculating last date for selected date range

shaileshp — Thu, 06 Oct 2022 13:41:15 GMT

Thanks daXtreme Will try and let you know.

Re: Power BI Dax issue in calculating last date for selected date range

shaileshp — Thu, 06 Oct 2022 15:32:56 GMT

Hi daXtreme

I have tried the suggested DAX, seems its not giving correct result.

I have selected date range as 1st Oct 2022 to 16th Oct 2022. Also slicer selected as "Weekday". I am expecting first date 1st Oct 2022 and Last Date as 16th Oct 2022 for all the weekday.

Unfortunately i am not able to upload sample file, but i have created calendar table with below dax and suggested dax.

Calender Dates = GENERATE(

CALENDAR( DATE(1900,1,1), DATE(2050,1,1) ),

VAR CurrentDay = [Date]

VAR dayNum = FORMAT(CurrentDay,"DD")

VAR monthNum = FORMAT( currentDay,"MM" )

VAR yearNum = YEAR ( currentDay )

Var yearText = CONCATENATE(yearNum,"")

VAR monthyear = CONCATENATE( CONCATENATE( FORMAT( currentDay,"MMM" ) , "-") , YEAR(CurrentDay) )

VAR quarteryear = CONCATENATE( CONCATENATE("Q", CONCATENATE( FORMAT( currentDay,"Q" ) , "-")) , YEAR(CurrentDay) )

VAR formattedDate = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(FORMAT( currentDay,"MMM" )," "),FORMAT(CurrentDay,"DD")),", "),FORMAT(CurrentDay,"YYYY"))

VAR weekday = FORMAT(CurrentDay,"ddd")

VAR DateID = CONCATENATE( CONCATENATE( yearNum,monthNum),dayNum)

VAR yearmonthID = CONCATENATE(yearNum,monthNum)

VAR WeekStartDate = CurrentDay - WEEKDAY(CurrentDay,1)+1

VAR WeekEndDate = CurrentDay - WEEKDAY(CurrentDay,1)+7

VAR WeekStartDayNum = FORMAT( WeekStartDate,"DD" )

VAR WeekStartMonthNum = MONTH(WeekStartDate)

VAR WeekStartYearNum = YEAR(WeekStartDate)

VAR WeekStartDatemonthyear = CONCATENATE( CONCATENATE( FORMAT( WeekStartDate,"DD" ) , "-") , FORMAT( WeekStartDate,"MMM" ) )

//VAR WeekStartYearMonthID = CONCATENATE(CONCATENATE(WeekStartyearNum,WeekStartmonthNum), WeekStartDayNum)

VAR WeekStartYearMonthID = FORMAT(WeekStartDate, "YYYYMMDD")

VAR DAYMONTHYEAR = CONCATENATE(CONCATENATE(CONCATENATE(FORMAT(CurrentDay,"MM"),"/"),CONCATENATE(FORMAT(CurrentDay,"DD"),"/")),YEAR(CurrentDay))

var Day_Month_Year = FORMAT(CurrentDay,"DD MMM YY")

VAR WeekID = format(WEEKNUM( [Date], 2 ), "00")

VAR IsWeekdayOrWeekend = IF(WEEKDAY(CurrentDay,2) IN {6,7},"Weekend","Weekday")

RETURN ROW(

"DateID",DateID,

"day",dayNum,

"monthNum", monthNum,

"year", yearNum,

"yearText",yearText,

"monthyear", monthyear,

"yearmonthID",yearmonthID,

"quarteryear",quarteryear,

"formattedDate",formattedDate,

"WeekdayName", weekday,

"WeekdayNum", WEEKDAY([Date],2),

"WeekStartDate", WeekStartDate,

"WeekEndDate", WeekEndDate,

"WeekStartDatemonthyear", WeekStartDatemonthyear,

"WeekStartYearMonthID", WeekStartYearMonthID,

"DAYMONTHYEAR", DAYMONTHYEAR,

"Day_Month_Year", Day_Month_Year,

"WeekID",WeekID,

"IsWeekdayOrWeekend",IsWeekdayOrWeekend

)

)

-------------------

FirstDate v2 =

var MinWeekID =

CALCULATE(

MIN( 'Calender Dates'[WeekID] ),

ALLSELECTED( 'Calender Dates' )

)

var Output =

CALCULATE(

MIN( 'Calender Dates'[Date] ),

'Calender Dates'[WeekID] = MinWeekID,

REMOVEFILTERS('Calender Dates' )

)

return

Output

-----------------

LastDate v2 =

var MaxWeekID =

CALCULATE(

MAX( 'Calender Dates'[WeekID] ),

ALLSELECTED( 'Calender Dates' )

)

var Output =

CALCULATE(

MAX( 'Calender Dates'[Date] ),

'Calender Dates'[WeekID] = MaxWeekID,

REMOVEFILTERS( 'Calender Dates' )

)

return

Output

----------Visuals

Appreciated your help on this.

Thanks.

Re: Power BI Dax issue in calculating last date for selected date range

daXtreme — Thu, 06 Oct 2022 21:06:16 GMT

You have to clearly define the logic behind your thinking and give good examples.

Re: Power BI Dax issue in calculating last date for selected date range

shaileshp — Wed, 12 Oct 2022 05:46:28 GMT

Hi daXtreme

Thank you for your reply.

Could you please download sample file from below link.

https://drive.google.com/file/d/17SxTofrDMijXZqxUdrWMSlloFNLcXouU/view?usp=sharing

There is one table visual which will require Weekday name, first date, and last date. User can select any period which is available under filters on all pages section. To show only weekdays i have selected only Weekday under one slicer and plan is we will hide it through selection pane. Now problem is if first date or last date from the selected period lies on saturday or sunday the on visual first date or last date consider as Friday, however user have selected sat and sun in period so it should consider for first date or last date.

Thanks.